How to Import Text Files with the Same Name and Schema But Different Directories into Database

Import multiple text files into SQL Server

declare @dir varchar(1000) = 'C:\Temp';
declare @command varchar(1000);
declare @files table (id int identity, filename varchar(1000));

set @command = 'dir /b ' + @dir;
insert into @files execute xp_cmdshell @command;

declare commands cursor for 
    select 'bulk insert <your table name here> from ''' + @dir + '\' + filename + ''' <other options, WITH FORMAT, etc. here>;' from @files;
open commands;
fetch commands into @command;
while (@@fetch_status = 0) begin
    --print @command;
    execute(@command);
    fetch commands into @command;
end;
close commands;
deallocate commands;

Modify @dir and the bulk insert command that is being build and you're done.

You may have to enable 'xp_cmdshell', and this could be a problem for your DBA; and using 'execute' is always a potential issue (SQL injection, etc.).

To enable xp_cmdshell:

-- To allow advanced options to be changed.  
EXEC sp_configure 'show advanced options', 1;  
GO  
-- To update the currently configured value for advanced options.  
RECONFIGURE;  
GO  
-- To enable the feature.  
EXEC sp_configure 'xp_cmdshell', 1;  
GO  
-- To update the currently configured value for this feature.  
RECONFIGURE;  
GO

how to import txt file into access database using vb6

This is pretty easy, and there is no reason to thunk over into the Desktop Text ODBC Driver because Jet 4.0 has a Text Installable ISAM that can do the job.

Examples: Append text to existing table, import text as new table.

Private Sub cmdAppend_Click()
    Dim lngRows As Long

    conDB.Execute _
          "INSERT INTO ExistingTable SELECT * " _
        & "FROM [Import.txt] IN '" & App.Path & "' 'TEXT;'", _
          lngRows, _
          adCmdText Or adExecuteNoRecords

    MsgBox CStr(lngRows) & " rows appended to ExistingTable."
End Sub

Private Sub cmdImport_Click()
    Dim lngRows As Long

    conDB.Execute _
          "SELECT * INTO NewTable " _
        & "FROM [Import.txt] IN '" & App.Path & "' 'TEXT;'", _
          lngRows, _
          adCmdText Or adExecuteNoRecords
    MsgBox CStr(lngRows) & " rows imported as NewTable."
End Sub

The key to making this work is to have a file named Schema.ini in the same folder as the text file (in this case I'm just using the App.Path folder for demonstration):

[Import.txt]
Format = Delimited( )
TextDelimiter = none
ColNameHeader = False
MaxScanRows = 0
Col1="Code" Text Width 9
Col2="Sequence" Text Width 10
Col3="Type" Integer Width 1

Above I made up field names and data types, which in a real case would match those in your database. Be sure to note the single blank space between the parentheses which will be the delimiting character used.

If you have several text files in the same folder to import just add additional INI sections to Schema.ini as needed.

Your program could even write such a Schema.ini to the folder dynamically if required, inserting the necessary text file name, list of fields and their names, type, sizes, etc.

How to easily import multiple sql files into a MySQL database?

In Windows, open a terminal, go to the content folder and write:

copy /b *.sql all_files.sql

This concate all files in only one, making it really quick to import with PhpMyAdmin.

In Linux and macOS, as @BlackCharly pointed out, this will do the trick:

cat *.sql  > .all_files.sql

Important Note: Doing it directly should go well, but it could end up with you stuck in a loop with a massive output file getting bigger and bigger due to the system adding the file to itself. To avoid it, two possible solutions.

A) Put the result in a separate directory to be safe (Thanks @mosh):

mkdir concatSql
cat *.sql  > ./concatSql/all_files.sql

B) Concat them in a file with a different extension and then change it the name. (Thanks @William Turrell)

cat *.sql  > all_files.sql1
mv all_files.sql1 all_files.sql

---

Related Topics