• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 956
  • Last Modified:

Import a fixed width file into SQL Server 2005

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.

Thanks,
Matt
FixedWidthTest.txt
0
dabdowb
Asked:
dabdowb
  • 8
  • 4
1 Solution
 
BanthorCommented:
Your data file is not exaclty fixed width, look for Ragged Right
I have been doing this kind of stuff for almost 30 years. I still use the wizard to make the base import. and then edit in SSIS if needed
I always save the end package in file format rather than in SQL server.
 
0
 
dabdowbAuthor Commented:
True, not exactly fixed width since one line has one set of data for the product number and then the next line has a different set of data for the same product.  However, it is all in a set specific format to load into our master software system.
The only line I am interested in dissecting is the 'A' line, and only about the first 50 - 100 characters...can something be setup to grab those and leave the remainder as whatever it is until end of line?
I can't get the wizard to come close to what I need at all, so at a loss there, and don't really know a thing about SSIS yet.  Just started using this software.  
0
 
dabdowbAuthor Commented:
In fact, I can tell you the breakdown of the first few parts of the line I need.  I will get this posted tomorrow am.

Thanks,
Matt
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
BanthorCommented:
You have to declare a column for all data. You can delete the leftovers afterword.
0
 
dabdowbAuthor Commented:
Sorry for the delayed response.  I tried to use the wizard again, this time, choosing ragged right, instead of fixed width, and I thought I was going to be successful.  Problem for me is even in trying the wizard, it doesn't seem to bring the data in correctly.  I get one row of data where the column 3, has the letter 'A' in it.  This is a key field for me, as the rows I want to ignore have the letter 'I' in that field.  Out of roughly 4.5 million records, half of them should be with the letter 'A' and the other half the letter 'I'.  It is almost like after the very first row, which had the 'A' in it, the column somehow get off.  Maybe because of the whole ragged right thing or how I setup the columns?  I must be doing something wrong, but can't figure out where?
It is one of the reasons why I wanted to try fixed width, and just tell it to grab the first few columns...which should be something like this (all string or nvarchar):
Column 0 - 6
Column 1 - 18
Column 2 - 18
Column 3 - 1
The rest of the data on any of those lines I don't care about at this point....maybe some other time, but I really need this part to work first.  I have tried to do the import the way above, and it keeps giving me error messages on the remainder of the column data.  Won't even import.
Thoughts?
0
 
dabdowbAuthor Commented:
sorry I didn't specify clearer.
Column 0 should be 6 characters long, Column 1 should be 18 characters long, etc.
 
Thanks,
0
 
BanthorCommented:
Your data is Ragged Right. That is the only format that will work for this.
Ragged Right means Fixed width, with the last column a varying width.
You can not ignore records in the import, you must import the data, then delete the un desired columns or rows.
There still has to be a column for all the data. your column 4 can be varchar(max) and store all the remaining data.
If you are going to do this more than a few times, you can save the SSIS package and then edit the package and skip importing the last column.
0
 
dabdowbAuthor Commented:
Varchar(max) sounds like a great solution for the Column 4, but it is not available that I see when using the import wizard.  Do I need to setup the table first and that would somehow help?  I know I did that when I would import delimited files and use the BULK INSERT command, but I don't know how to use the BULK INSERT command with fixed width, or ragged right options, only delimited (which maybe that is the only way).
I understand what you are saying conceptually, and have been trying to do that for weeks now with no success, hence my post.  If I could successfully get all the data to come in, I have no issue at all cleaning it up afterward...I just can't get the data in correctly.
I go to the Import Wizard, I choose flat file for my source, I point to the file.  Unicode box is unchecked, Code Page shows 1252 (ANSI - Latin 1), Format I show Ragged Right, Text Qualifier set to None, Header Row Delimiter shows {CR}{LF}, Header Rows to Skip shows Zero, Column Names in the first row data row is unchecked.
The next screen, I choose my lines (field seperators) as shown above in previous post, and Row Delimiter is set to {LF}.  I then go to Advanced, to setup the data types.  Columns 0 through 3 are all set correct field lengths, data type is string [DT_STR] and text qualified shows as False.  All three of those columns show they column type as Fixed Width (which is greyed out).  
Then comes the difficulty, Column 4, that should just have all the rest of the data for that line until it sees the end of line.  It shows a column type of Delimited (which too is greyed out), Column Delimiter is set to {LF}, Data Type is set to string [DT_STR], OutputColumnWidth defaults to 50, but I set it to 500...have even tried 1000, and TextQualified is set to True (have tried False with no success either).
Now I check the Preview, and it looks great!!  Exactly how I want the end result to be, but if I follow through the rest of the screens with the default options set, it either does not work at all, or if it does actually build the table, only one row shows up correctly, the first row, with all the remaining rows imported incorrectly.  Thus, I cannot clean the data because I don't have all the data I need to do so.
It is maddening how something SO simple, will not cooperate.  Have you tried to do the import on the sample text I provided?  Did you have Column 3 showing A, then I, then A then I then A then I, for each row of data in the end table?  If so, what did I miss in my above steps??
Thanks
0
 
dabdowbAuthor Commented:
One thing I did notice, that I thought odd, when you go to select the column seperators, the screen you see with a few lines of data to help you choose where to put the seperators, was 32005 column lengths wide, even though the data only goes out about 600 column lengths.  One of the error messages I get on column 4 frequently is that it thinks it will lose some data somehow.  
0
 
BanthorCommented:
varchar(max) should be available in SQL 2005 Standard and above.
I may have access to a SQL 2005 machine Thursday to check on the column widths
I am running 2008 here in the lab
0
 
dabdowbAuthor Commented:
Any luck with the 2005 version?
Thanks,
Matt
0
 
dabdowbAuthor Commented:
Update:
I decided to delve back in and try this again today.  That is when I realized that the table I was trying to insert to was not setup to receive the same number of characters I was trying to load...hence the truncation errors.  Also, saw the varchar(MAX) option on the table in SQL...I thought this was supposed to be an option on the Import Wizard, hence my confusion.
Anyway, I made the changes, and success!
Thanks for the help....and patience with this newbie!
Cheers,
Matt
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 8
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now