I am attempting to import a fixed width text file into SQL Server 2005. I have had my fair share of challenges with such an easy task even on delimited files due to issues with converting different types of data between source and destination.
If you can't tell, I am new to SQL Server in general, so everything seems challenging, even most basic things. I managed to finally get pipe delimited files into SQL using the BULK INSERT command, but this file is not delimited, but very sizable (about 3.5GB in size and roughly 600 characters wide at some points) so looking for a solution that will get the data in quickly.
I tried the import wizard, and really have interest right now in only about the first 100 characters of the file, but when I setup the mapping to specifically grab so many characters into this field, and so many into this field, etc., and then got to a point where the rest of the file could all just come down as one big mess, it didn't work right.
In fact, if I looked at the preview, instead of the data showing horizontal, it seemed to switch stuff to a vertical view instead. I could expand, shrnk the column lengths and then more data would start showing horizontal again, but never got it all to sink in correctly.
Any thoughts on how to get this file in? I figure SSIS would be a great route, but not familiar with it at all at this point. I have attached a small sample of the file below so the basic structure can be seen. Just to save alot of questions, you will notice that most items have two lines, and about 43 characters in or so, you will see one row has the letter 'A' and the next one for the same material, has the letter 'I'. I do not care about pulling in the 'I' lines at this point, only the 'A' lines, but I understand that I need to bring them all in. As such, I worked as though only the 'A' line existed, knowing that that one field, of one character length, I could simply go in and delete the lines with 'I' in them after the fact.
Geez, I better shut up now. Let me know of more questions.