fixed width - dynamically set column widths

fixed width - dynamically set column widths

Post by Duke Care » Sun, 30 Mar 2003 23:49:20



A data provider sends my client a fixed width file - 70+ fields and over 600
characters/line.

Right now the client uses a VB6 program that parses the data and loads it to
SQL.  I don't have the source code (neither does the client), and the
documentation of field widths in this file is clearly wrong.   Worse, the
layout of the file is supposed to be changing in the near future.

I'd like to use DTS and be able to experiment with different settings, but
modifying the settings for 70+ fields isn't just tedious, it's really error
prone.

Is there an easier way to do this besides dragging the little arrows back &
forth?

Does the DTS programming object offer some functionality in this regard.
I've spend a little time scrolling through some of the methods and
properties but haven't found any references to this issue.

Thanks
Duke

 
 
 

fixed width - dynamically set column widths

Post by hsali » Tue, 01 Apr 2003 11:14:39


Create a DSN (ODBC) pointing to your text file.
You can specify that it is fixed width file.
You can also create column alsiases if you like - otherwise the columns are
named Col1, Col2...

When you are done, you will notice that the system adds a Schema.ini file to
the same folder.

If you prefer (as I do), you can then edit the schema.ini directly using any
text editor such as notepad.

Regards
HS


| A data provider sends my client a fixed width file - 70+ fields and over
600
| characters/line.
|
| Right now the client uses a VB6 program that parses the data and loads it
to
| SQL.  I don't have the source code (neither does the client), and the
| documentation of field widths in this file is clearly wrong.   Worse, the
| layout of the file is supposed to be changing in the near future.
|
| I'd like to use DTS and be able to experiment with different settings, but
| modifying the settings for 70+ fields isn't just tedious, it's really
error
| prone.
|
| Is there an easier way to do this besides dragging the little arrows back
&
| forth?
|
| Does the DTS programming object offer some functionality in this regard.
| I've spend a little time scrolling through some of the methods and
| properties but haven't found any references to this issue.
|
| Thanks
| Duke
|
|

 
 
 

fixed width - dynamically set column widths

Post by Duke » Wed, 02 Apr 2003 09:34:24


HS -

You're a lifesaver.  Took a few minutes to figure out exactly what to do,
but it worked like a dream, and I can quickly try different settings to get
the data into the table.

Thanks
Duke


> Create a DSN (ODBC) pointing to your text file.
> You can specify that it is fixed width file.
> You can also create column alsiases if you like - otherwise the columns
are
> named Col1, Col2...

> When you are done, you will notice that the system adds a Schema.ini file
to
> the same folder.

> If you prefer (as I do), you can then edit the schema.ini directly using
any
> text editor such as notepad.

> Regards
> HS



> | A data provider sends my client a fixed width file - 70+ fields and over
> 600
> | characters/line.
> |
> | Right now the client uses a VB6 program that parses the data and loads
it
> to
> | SQL.  I don't have the source code (neither does the client), and the
> | documentation of field widths in this file is clearly wrong.   Worse,
the
> | layout of the file is supposed to be changing in the near future.
> |
> | I'd like to use DTS and be able to experiment with different settings,
but
> | modifying the settings for 70+ fields isn't just tedious, it's really
> error
> | prone.
> |
> | Is there an easier way to do this besides dragging the little arrows
back
> &
> | forth?
> |
> | Does the DTS programming object offer some functionality in this regard.
> | I've spend a little time scrolling through some of the methods and
> | properties but haven't found any references to this issue.
> |
> | Thanks
> | Duke
> |
> |

 
 
 

1. Row width limit when importing fixed width files (SQL 7.0)

I am attempting to import a datafeed file into a MSSQL 7.0 database.
The file was exported from Oracle (not sure which version) as a fixed
width text file.  The block size(row width) of the records in the text
file is 5610.  When I am defining the columns during the import I
cannot define a column past position 4096 in MSSQL 7.0.  Is there any
way around this limitation?

Adam

2. US-NC-Visual FoxPro Developer (Internet), DB Basics, Inc. #Sandra 206

3. DTS error - fixed field width - considers width of first line only

4. How to copy 10,000 memo fields P.S.

5. Total width of columns = Width of ListView

6. SP_OACREATE fails , Help!!!

7. PLS:how to Config and Ensure that the character set is of fixed width before

8. Help! Running Paradox Dos 4.5 in Win 95

9. Creating fixed-width column output

10. Import Wiz Fixed width column indicator limit?

11. SQL Server 7 - fixed width column export

12. BULK INSERT fixed width columns - get truncation error

13. Unload command, fixed width columns