SQLTeam.com | Weblogs | Forums

How do I load a .txt file into SQL server

Hi all

I am trying to load a very large .txt file into sql server. Do need to download bcp or is there another way around it? I tried using the "task", "import data" way but the file is on an encrypted hard drive that only stays open for 5 minutes at a time if its inactive and by the time I do all the steps the drive locks up. Any assistance with this would be greatly appreciated.

Thanks

1 Like

Welcome

Do you permission to copy the file out to another location?
Is that a delimited file, by comma, by tab or something else?
Do you know the column names in the file?

depending on your experience you can also use

  • powershell
  • python
  • bcp

etc

If you use this:

Save and Run Package (SQL Server Import and Export Wizard) - SQL Server Integration Services (SSIS) | Microsoft Docs

you can create an import first and then you can run it later for real. If you have to it once I would import the data into Excel and import it from excel so you don't have to worry about the 5 minutes.

Thanks Yosiazz. I tried doing that initially, but at the 3 hour mark the download errored out. The person who did this before left the company and didnt leave any guidance or instructions before leaving but I did see something about bcp (whatever that is).

Thanks Rogier, but the copy errored out after copying over 3 hours.

how large is the file? ad you didn't answer the following questions.

Is that a delimited file, by comma, by tab or something else?
Do you know the column names in the file?

Sorry Yosiazz..I neglected to answer your other questions. It's delimited by "*" and yes I know the column names in the file

Yosiazz, the file size is 923,450,939 kb

create a sample file identical to the remote file , for example locally on your C drive.
:scream: yuge file

create the columns of the file identical to the remote file
add some sample data to the file, identical to the remote file (2 or 3 rows)

create (and save at the end) an SSIS package that imports this sample file into SQL Server.

Once it works fine, point it to the real file and try it

Yosiazz, the problem with that is that there are 177 columns and I don't know how those columns are populated. I only know the column names because the vendor (in this case the state) sent along a list of the column names that are included.

Doesn't matter, as long as you know what the columns are you can populate it with bogus /sample/test data and exercise the import SSIS. it is just to do the exercise of importing it. Since the 5min doesn't give you enough time to do the wizard, this will help you do it on a local file since you wont be limited by time constraint. Then you know it works you then point it to the real file.

Thanks Yosiasz I was able import the sample file but it doesnt allow me to save an SSIS package from the wizard

if you care to share what you see it would help us help you. remember we are not sitting right next to you or have some magical powers to see what is happening on your PC. the more you share the more we are able to help you. your posts lack detailed clarity

screen shots would help in this case

these are the screen shots after trying to do the load from the hard drive to the sql server.

The file is one of many that were delimited by * so the chances that this one is as well is highly likely and there was no option for that in the wizard

image

I was in the process of making the screen shots when I try to make the SSIS package. That fails.

in the following step click on Columns then choose the delimiter of type *

you have to specify the delimiter here

SUCCESS!!!!!! Thank you very very much