asked
Most efficient way to load fixed width flat file into SQL
Our vendors only send us text files in fixed width format. It will be millions of records with 200+ columns. Yes, I have loaded them using SSIS 2005 up to 2012. There has got to be a more efficient approach to load the files without having to click every column in import tool. Any suggestions? Thanks!
What do you mean by "click every column in import tool"? Also, are you looking for processing efficiency, developer efficiency, or both? Finally, are all of the files using the same format (or possibly one format per vendor)?
I was thinking about SSIS, bpc, and BULK INSERT (with and without format files). One way or another, you need to know the structure in order to process it. SSIS certainly gives me headaches some days, but overall I love it. I feel like we need more information in order to properly answer this question.
@Tom Staab - are you thinking about `BULK INSERT`?
https://msdn.microsoft.com/en-GB/library/ms188365(v=sql.110).aspx
Basically, the format is fixed width or ragged right. We receive files daily from vendors that contain anywhere from 5 to 100's of fields that vary in length. We do receive a layout or specs for the file (name of field, length, start and stop position). What I mean by click, is if you import a fixed width file, you get the ruler option where the first field is say length 10, you click at position 10 and then go to field 2. If you have 200 or 300 fields, this is cumbersome. I would like to read a layout and then load the data. Thanks!
Thanks for the additional information. I suggest you look into format files. I prefer the XML type. Format files can be used with T-SQL using BULK INSERT or INSERT FROM OPENROWSET or from the command prompt using bcp. Maybe you can find a way to translate from their layout file and generate the appropriate format file. Use a Format File to Bulk Import Data (SQL Server)
[ https://technet.microsoft.com/en-us/library/ms178129(v=sql.110).aspx][1] XML Format Files (SQL Server)
[ https://technet.microsoft.com/en-us/library/ms187833(v=sql.110).aspx][2] [1]: https://technet.microsoft.com/en-us/library/ms178129(v=sql.110).aspx [2]: https://technet.microsoft.com/en-us/library/ms187833(v=sql.110).aspx
[ https://technet.microsoft.com/en-us/library/ms178129(v=sql.110).aspx][1] XML Format Files (SQL Server)
[ https://technet.microsoft.com/en-us/library/ms187833(v=sql.110).aspx][2] [1]: https://technet.microsoft.com/en-us/library/ms178129(v=sql.110).aspx [2]: https://technet.microsoft.com/en-us/library/ms187833(v=sql.110).aspx
19 People are following this question.
