Hi, I have a table with almost 600 columns & a fixed-width text file. I tried to import data from that text file to sql server table with Import Wizard, but it only supports first 20 columns. Is there any way I can convert fixed-width text to delimited text?
I'm using Microsoft SQL Server 2000. Thank you in advance
Hyung Rhoe
Wed, 09 Jul 2003 02:35:09 GMT
Thomas Remku #2 / 4
Convert fixed-length text file to delimited text
Look into Bulk Insert.
Hi, I have a table with almost 600 columns & a fixed-width text file. I tried to import data from that text file to sql server table with Import Wizard, but it only supports first 20 columns. Is there any way I can convert fixed-width text to delimited text?
I'm using Microsoft SQL Server 2000. Thank you in advance
Hyung Rhoe
Wed, 09 Jul 2003 03:04:31 GMT
Hyung Rho #3 / 4
Convert fixed-length text file to delimited text
Hi, again. I tried Bulk Insert with DTS, but I got the error message like this: --------------------------------------------------------------------------------- The statement has been terminated. OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error. Bulk Insert fails. Column too long in data file for row 1, column0. Make sure FieldTerminator and RowTerminator was specified correctly. ---------------------------------------------------------------------------------
The size of source text file is 754MB, so I could'n open this file with MS Word (It has 32MB file size limitation), and I don't know what it looks like. However, I colud see 5 rows (1,583 characters in each row) of data when I tried the Import Wizard. This data was shown at "Fixed Field Column Positions" window and those were divided with 20 upward arrows. (which was wrong, because it should be divided around 580 columns) I can make another 560 arrows by myself for this particular data, but I will have 50 more this kind of text file. Also, some columns have to be converted to different data types according to their character values later on. So, I need to convert this fixed-length text file to comma delimited text file. Does anybody have a good idea?
Quote:
-----Original Message-----
Look into Bulk Insert.
-----Original Message----- Look into Bulk Insert.
Hi, I have a table with almost 600 columns & a fixed-width text file. I tried to import data from that text file to sql server table with Import Wizard, but it only supports first 20 columns. Is there any way I can convert fixed-width text to delimited text?
I'm using Microsoft SQL Server 2000. Thank you in advance
Hyung Rhoe
.
Wed, 09 Jul 2003 05:29:26 GMT
kontiki3.. #4 / 4
Convert fixed-length text file to delimited text
Quote:
> Hi, > I have a table with almost 600 columns & a fixed-width text file. > I tried to import data from that text file to sql server table with = > Import Wizard, > but it only supports first 20 columns. > Is there any way I can convert fixed-width text to delimited text?
A generic parser/converter like ParseRat (http://www.guysoftware.com/parserat.htm) can do this.
Convert fixed-length text file to delimited text