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

SSIS and Import/Export
1

HI all

I have a huge .txt file that was so large I had to break it down into multiple files. I imported the first file into SQL and created a table using the import wizard, and now I want to add the other files to that table. Can anyone help me with this?

Thanks

2

Use wizard again, making sure to point to same target table but also making sure it does not do drop and create same table

3

Thanks @yosiasz but I already did that and it comes back with this error:
TITLE: SQL Server Import and Export Wizard

Could not connect source component.

Error 0xc0207015: Source - File1-2_txt [1]: There is more than one data source column with the name "3". The data source column names must be unique.

ADDITIONAL INFORMATION:

Exception from HRESULT: 0xC0207015 (Microsoft.SqlServer.DTSPipelineWrap)

BUTTONS:

OK

4

It also tells me that I am appending an existing table, (the one I am actually appending), so I think I am doing it correctly...I have a feeling that when I split the larger file, it didn't split correctly. I say that because I split the file into files with 10m rows. The upload went through and said 10m rows were loaded, but when I run a row count query on the table it only has 5m rows

5

What are the column names in the second file?

6

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.

7

How big is the original file before you dig yourself deeper

8

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..

9

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

10

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/

11

The target server is remote

12

@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.

13

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

14

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?

15

And where are the files located at?

16

They are located in a encrypted external hard drive

17

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

18

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

19

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

20

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