importing fixed width files with 80 fields

jorbroni
jorbroni used Ask the Experts™
on

Here is the situation. Using the import tool, I have to define 80 fields in order to import the flat file data into my db table. I problem is that I have to define 80 fields for my fixed width file. Do any of you have a better and quicker way of doing this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Hi,

You could create an schema.ini file, see
http://msdn.microsoft.com/en-us/library/windows/desktop/ms709353(v=vs.85).aspx
so you don't have to recreate the definition every time.

Example schema.ini:

[test.txt]
Format=FixedLength
ColNameHeader=False
MaxScanRows=0
Col1=col1 Text Width 4
Col2=col2 Text Width 4
Col3=col3 Text Width 4

Open in new window


I'm not sure if it works with flat file source though, but if you have a text-driver that might work.

Otherwise you could run an INSERT like this

INSERT INTO yourtable
SELECT * 
FROM   OPENROWSET('MSDASQL', 
   'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=d:\;', 
   'SELECT * FROM test.txt;' 
) 

Open in new window


/peter

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial