How do I add additional .txt files into a SQL table a .txt file into SQL server

What are the column names in the second file?

You know I just thought of something when you asked that question. When one splits a text file into multiple files, do the column names also get added? If not that's probably my issue. Now I need to find out how to add the column headings to all the other files.

How big is the original file before you dig yourself deeper

923,450,939 kb
1,537,045,275 rows
176 columns

I cant just load the original document because I work remotely and have to log in to a VPN..the VPN expires after 12 hours. So I thought it would be easier to split the big file into 16 100m row files. That size also takes longer to load than my VPN expiration, so I broke that down to 10, 10m row files...those are the ones I am loading..

And where is the target server you sre uploading this to ,local or remote?

hi

+++++++++
please split the hue text file .. seperately .. on local disk
and then import all the files into SQL Server using POWERShell ( any scripting language) or SSIS
the below are for excel files you change to text files
++++
OnLINE tool

https://www.sqlshack.com/import-data-from-multiple-excel-files-in-sql-server-tables-using-an-ssis-package/

The target server is remote

@harishgg1, I am not sure what you mean by split the hue file. Also, the large file is a .txt file not excel with 1.5 billion rows. Excel can only take 1 million rows...I would need to make thousands of excel files...that's not an option. In addition I already split the large file into smaller ones. The headings row is only applying to the first split file and not the others.

hi

create a seperate file with the HEADINGS example 123.txt

now append this to the begining of each file ..
example: small1 , small2 , small3 , small4

123.txt + small1
123.txt+small2
123.txt+small3
123.txt+small4

oh ok..I see what you are saying. Do you know if there is a way to do the load automatically, meaning when one file is done, it immediately starts loading the next file without me having to start it?

And where are the files located at?

They are located in a encrypted external hard drive

So do you see the issue I am trying to point out?

Trying to push a huge file, over limited time vpn, across the wires to a remote sql server from an external hard drive. You are a brave soul but this will be very difficult. Might have been better (if that was possible at all to copy the smaller poeves of files)to have that file on a local drive of the sql server itself. Then it all happens locally on the server. If you want to pick up the next file implement a loop via ssis ,powershell or python. So then you can start the process of the load on the server itself and you can go have coffee or even a big lunch and come back when it finishes when the automated process sends you an email

LOL..thanks @yosiasz but I already knew all of that. I was trying to see if the community had a better way of doing it that's why I came to you guys. The way it looks now I would have to create at least 1,500 individual files that would be small enough to open to copy the headings to which I'm not going to do because it would be ridiculous. Do me a favor and email my boss...she just wont get over the fact that it cannot be done by us and that we need to hand the hard drive over to IT..lol

Easy to do. What are more comfortable with? Powershell python or ssis

one way is to generate a script that will do it

example
files = sample1 ,sample2 , sample3

generate script

copy sample1 to xyz
copy sample2 to xyz
copy sample3 to xyz

I am not creating 1,500 files...that I would then have to open and add a heading...

For the loads after / other than the first load, specify that headings are NOT included in the file. Since you're appending to an existing table, you don't need column names anyway.

1 Like

who said you need to.

when I tired to append the table earlier the upload failed because it didn't have a heading