Flat File import

FAQ

Profile

Flat File import

Database Forums (Home) -> DTS

Next: Problem connecting to a local SSIS Service

Author

Message

Bigal

External

Since: Dec 27, 2005
Posts: 6

(Msg. 1) Posted: Tue Dec 27, 2005 1:37 pm Post subject: Flat File import Archived from groups: microsoft>public>sqlserver>dts (more info?)

Hello, I need to import data from a flat file into a sql server 2000 database. The Fate file has no delimiters. I have the specs for this file so I do knowthe layout of this file. Can somebody point me in the rigvht direction to accomplish this. Thanks >> Stay informed about: Flat File import

Andrew J. Kelly

External

Since: Sep 01, 2003
Posts: 975

(Msg. 2) Posted: Tue Dec 27, 2005 4:55 pm Post subject: Re: Flat File import [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Just use the DTS import wizard and follow the prompts. You say it is not delimited so it must be fixed width which the wizard usually figures out on it's own. Go to tools - Import in EM. -- Andrew J. Kelly SQL MVP "Bigal" wrote in message > Hello, > > I need to import data from a flat file into a sql server 2000 database. > The > Fate file has no delimiters. I have the specs for this file so I do > knowthe > layout of this file. Can somebody point me in the rigvht direction to > accomplish this. > > Thanks >> Stay informed about: Flat File import

Bigal

External

Since: Dec 27, 2005
Posts: 6

(Msg. 3) Posted: Tue Dec 27, 2005 6:39 pm Post subject: Re: Flat File import [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Thanks for your reply. The file is fixed but the data is not properly lined up to import it the way it is. "Andrew J. Kelly" wrote: > Just use the DTS import wizard and follow the prompts. You say it is not > delimited so it must be fixed width which the wizard usually figures out on > it's own. Go to tools - Import in EM. > > -- > Andrew J. Kelly SQL MVP > > > "Bigal" wrote in message > > > Hello, > > > > I need to import data from a flat file into a sql server 2000 database. > > The > > Fate file has no delimiters. I have the specs for this file so I do > > knowthe > > layout of this file. Can somebody point me in the rigvht direction to > > accomplish this. > > > > Thanks > > > >> Stay informed about: Flat File import

Andrew J. Kelly

External

Since: Sep 01, 2003
Posts: 975

(Msg. 4) Posted: Tue Dec 27, 2005 10:38 pm Post subject: Re: Flat File import [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

It is either fixed length or it is not. If all the columns are not the same width in all the rows for each column it is not a fixed width. Or the file needs manual work to get it there. Without delimiters you need some way to determine where one column starts and one ends. -- Andrew J. Kelly SQL MVP "Bigal" wrote in message > Thanks for your reply. The file is fixed but the data is not properly > lined > up to import it the way it is. > > "Andrew J. Kelly" wrote: > >> Just use the DTS import wizard and follow the prompts. You say it is not >> delimited so it must be fixed width which the wizard usually figures out >> on >> it's own. Go to tools - Import in EM. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Bigal" wrote in message >> >> > Hello, >> > >> > I need to import data from a flat file into a sql server 2000 database. >> > The >> > Fate file has no delimiters. I have the specs for this file so I do >> > knowthe >> > layout of this file. Can somebody point me in the rigvht direction to >> > accomplish this. >> > >> > Thanks >> >> >> >> Stay informed about: Flat File import

Bigal

External

Since: Dec 27, 2005
Posts: 6

(Msg. 5) Posted: Wed Dec 28, 2005 6:21 am Post subject: Re: Flat File import [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Dont know the terminology so I will explain this the best I can. Every field is in a fixed postion. See example Field Type RG Line Column Width Print Width ----------------------------------------------------------------------------------------- :WALM816Btest: Literal Text 0 1 1 0 10 0000 Literal Text 0 2 1 1 4 BHT02 AlphaNumeric 0 2 5 2 2 BHT01 AlphaNumeric 0 2 7 4 4 BHT03 AlphaNumeric 0 2 11 50 50 "Andrew J. Kelly" wrote: > It is either fixed length or it is not. If all the columns are not the same > width in all the rows for each column it is not a fixed width. Or the file > needs manual work to get it there. Without delimiters you need some way to > determine where one column starts and one ends. > > -- > Andrew J. Kelly SQL MVP > > > "Bigal" wrote in message > > > Thanks for your reply. The file is fixed but the data is not properly > > lined > > up to import it the way it is. > > > > "Andrew J. Kelly" wrote: > > > >> Just use the DTS import wizard and follow the prompts. You say it is not > >> delimited so it must be fixed width which the wizard usually figures out > >> on > >> it's own. Go to tools - Import in EM. > >> > >> -- > >> Andrew J. Kelly SQL MVP > >> > >> > >> "Bigal" wrote in message > >> > >> > Hello, > >> > > >> > I need to import data from a flat file into a sql server 2000 database. > >> > The > >> > Fate file has no delimiters. I have the specs for this file so I do > >> > knowthe > >> > layout of this file. Can somebody point me in the rigvht direction to > >> > accomplish this. > >> > > >> > Thanks > >> > >> > >> > > > >> Stay informed about: Flat File import

Andrew J. Kelly

External

Since: Sep 01, 2003
Posts: 975

(Msg. 6) Posted: Wed Dec 28, 2005 9:43 am Post subject: Re: Flat File import [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Now you have lost me even more<g>. Are you saying that a data row spans multiple rows in the file? Can you just post a sample of the actual file? -- Andrew J. Kelly SQL MVP "Bigal" wrote in message > Dont know the terminology so I will explain this the best I can. Every > field > is in a fixed postion. See example > > Field Type RG Line Column > Width > Print Width > ----------------------------------------------------------------------------------------- > :WALM816Btest: Literal Text 0 1 1 0 10 > > 0000 Literal Text 0 2 1 1 > 4 > BHT02 AlphaNumeric 0 2 5 2 > 2 > > BHT01 AlphaNumeric 0 2 7 4 > 4 > BHT03 AlphaNumeric 0 2 11 50 > 50 > > > "Andrew J. Kelly" wrote: > >> It is either fixed length or it is not. If all the columns are not the >> same >> width in all the rows for each column it is not a fixed width. Or the >> file >> needs manual work to get it there. Without delimiters you need some way >> to >> determine where one column starts and one ends. >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Bigal" wrote in message >> >> > Thanks for your reply. The file is fixed but the data is not properly >> > lined >> > up to import it the way it is. >> > >> > "Andrew J. Kelly" wrote: >> > >> >> Just use the DTS import wizard and follow the prompts. You say it is >> >> not >> >> delimited so it must be fixed width which the wizard usually figures >> >> out >> >> on >> >> it's own. Go to tools - Import in EM. >> >> >> >> -- >> >> Andrew J. Kelly SQL MVP >> >> >> >> >> >> "Bigal" wrote in message >> >> >> >> > Hello, >> >> > >> >> > I need to import data from a flat file into a sql server 2000 >> >> > database. >> >> > The >> >> > Fate file has no delimiters. I have the specs for this file so I do >> >> > knowthe >> >> > layout of this file. Can somebody point me in the rigvht direction >> >> > to >> >> > accomplish this. >> >> > >> >> > Thanks >> >> >> >> >> >> >> >> >> >> Stay informed about: Flat File import

Bigal

External

Since: Dec 27, 2005
Posts: 6

(Msg. 7) Posted: Wed Dec 28, 2005 9:43 am Post subject: Re: Flat File import [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Sample: :WALM816B: 0000000065200512251206N000000 12/25/2005WAL-MART STORES, INC. 10000000001 35 1100CQ WAL-MART STORES 0078742000008 HOME OFFICE 702 SW 8TH STREET BENTONVILLE AR72716 US 0519577690000 10000000002 0000001 36 1100SN WAL-MART SUPERCENTER 0001 0078742000015 2110 WEST WALNUT ROGERS AR72756 US 479-636-3222 0519577690001 00001 10000000003 0000001 36 1100SN WAL-MART SUPERCENTER 0002 0078742000022 1417 HWY 62/65 N HARRISON AR72601 US 870-365-8400 0519577690002 00002 "Andrew J. Kelly" wrote: > Now you have lost me even more<g>. Are you saying that a data row spans > multiple rows in the file? Can you just post a sample of the actual file? > > -- > Andrew J. Kelly SQL MVP > > > "Bigal" wrote in message > > > Dont know the terminology so I will explain this the best I can. Every > > field > > is in a fixed postion. See example > > > > Field Type RG Line Column > > Width > > Print Width > > ----------------------------------------------------------------------------------------- > > :WALM816Btest: Literal Text 0 1 1 0 10 > > > > 0000 Literal Text 0 2 1 1 > > 4 > > BHT02 AlphaNumeric 0 2 5 2 > > 2 > > > > BHT01 AlphaNumeric 0 2 7 4 > > 4 > > BHT03 AlphaNumeric 0 2 11 50 > > 50 > > > > > > "Andrew J. Kelly" wrote: > > > >> It is either fixed length or it is not. If all the columns are not the > >> same > >> width in all the rows for each column it is not a fixed width. Or the > >> file > >> needs manual work to get it there. Without delimiters you need some way > >> to > >> determine where one column starts and one ends. > >> > >> -- > >> Andrew J. Kelly SQL MVP > >> > >> > >> "Bigal" wrote in message > >> > >> > Thanks for your reply. The file is fixed but the data is not properly > >> > lined > >> > up to import it the way it is. > >> > > >> > "Andrew J. Kelly" wrote: > >> > > >> >> Just use the DTS import wizard and follow the prompts. You say it is > >> >> not > >> >> delimited so it must be fixed width which the wizard usually figures > >> >> out > >> >> on > >> >> it's own. Go to tools - Import in EM. > >> >> > >> >> -- > >> >> Andrew J. Kelly SQL MVP > >> >> > >> >> > >> >> "Bigal" wrote in message > >> >> > >> >> > Hello, > >> >> > > >> >> > I need to import data from a flat file into a sql server 2000 > >> >> > database. > >> >> > The > >> >> > Fate file has no delimiters. I have the specs for this file so I do > >> >> > knowthe > >> >> > layout of this file. Can somebody point me in the rigvht direction > >> >> > to > >> >> > accomplish this. > >> >> > > >> >> > Thanks > >> >> > >> >> > >> >> > >> > >> > >> > > > >> Stay informed about: Flat File import

Bigal

External

Since: Dec 27, 2005
Posts: 6

(Msg. 8) Posted: Wed Dec 28, 2005 9:43 am Post subject: Re: Flat File import [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Here is a sample :WALM816B: 0000000065200512251206N000000 12/25/2005WAL-MART STORES, INC. 10000000001 35 1100CQ WAL-MART STORES 0078742000008 HOME OFFICE 702 SW 8TH STREET BENTONVILLE AR72716 US 0519577690000 10000000002 0000001 36 1100SN WAL-MART SUPERCENTER 0001 0078742000015 2110 WEST WALNUT ROGERS AR72756 US 479-636-3222 0519577690001 00001 10000000003 0000001 36 1100SN WAL-MART SUPERCENTER 0002 0078742000022 1417 HWY 62/65 N HARRISON AR72601 US 870-365-8400 0519577690002 00002 "Andrew J. Kelly" wrote: > Now you have lost me even more<g>. Are you saying that a data row spans > multiple rows in the file? Can you just post a sample of the actual file? > > -- > Andrew J. Kelly SQL MVP > > > "Bigal" wrote in message > > > Dont know the terminology so I will explain this the best I can. Every > > field > > is in a fixed postion. See example > > > > Field Type RG Line Column > > Width > > Print Width > > ----------------------------------------------------------------------------------------- > > :WALM816Btest: Literal Text 0 1 1 0 10 > > > > 0000 Literal Text 0 2 1 1 > > 4 > > BHT02 AlphaNumeric 0 2 5 2 > > 2 > > > > BHT01 AlphaNumeric 0 2 7 4 > > 4 > > BHT03 AlphaNumeric 0 2 11 50 > > 50 > > > > > > "Andrew J. Kelly" wrote: > > > >> It is either fixed length or it is not. If all the columns are not the > >> same > >> width in all the rows for each column it is not a fixed width. Or the > >> file > >> needs manual work to get it there. Without delimiters you need some way > >> to > >> determine where one column starts and one ends. > >> > >> -- > >> Andrew J. Kelly SQL MVP > >> > >> > >> "Bigal" wrote in message > >> > >> > Thanks for your reply. The file is fixed but the data is not properly > >> > lined > >> > up to import it the way it is. > >> > > >> > "Andrew J. Kelly" wrote: > >> > > >> >> Just use the DTS import wizard and follow the prompts. You say it is > >> >> not > >> >> delimited so it must be fixed width which the wizard usually figures > >> >> out > >> >> on > >> >> it's own. Go to tools - Import in EM. > >> >> > >> >> -- > >> >> Andrew J. Kelly SQL MVP > >> >> > >> >> > >> >> "Bigal" wrote in message > >> >> > >> >> > Hello, > >> >> > > >> >> > I need to import data from a flat file into a sql server 2000 > >> >> > database. > >> >> > The > >> >> > Fate file has no delimiters. I have the specs for this file so I do > >> >> > knowthe > >> >> > layout of this file. Can somebody point me in the rigvht direction > >> >> > to > >> >> > accomplish this. > >> >> > > >> >> > Thanks > >> >> > >> >> > >> >> > >> > >> > >> > > > >> Stay informed about: Flat File import

Andrew J. Kelly

External

Since: Sep 01, 2003
Posts: 975

(Msg. 9) Posted: Wed Dec 28, 2005 11:06 am Post subject: Re: Flat File import [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

It's hard to tell what it really looks like since the ng wraps text. But other than the first part ":WALM816B:" the rest of it looks like a typical fixed width format. You may be able to get by using the wizard and this file as is if you tell the wizard to skip the first line. Otherwise you may have to preprocess the file and remove that line. Once you deal with that first line the wizard should automatically pick up on the width of each column. If not you can manually set the width by dragging the column lines around in the gui. Have you tried the wizard? If so what are the actual problems you are having? -- Andrew J. Kelly SQL MVP "Bigal" wrote in message > Sample: > > :WALM816B: > 0000000065200512251206N000000 > 12/25/2005WAL-MART STORES, INC. > 10000000001 35 > 1100CQ WAL-MART STORES > 0078742000008 HOME OFFICE > 702 SW 8TH STREET > BENTONVILLE AR72716 US > > 0519577690000 > > > 10000000002 0000001 36 > 1100SN WAL-MART SUPERCENTER 0001 > 0078742000015 2110 WEST > WALNUT > > ROGERS AR72756 US > 479-636-3222 > 0519577690001 > 00001 > > 10000000003 0000001 36 > 1100SN WAL-MART SUPERCENTER 0002 > 0078742000022 1417 HWY 62/65 > N > > HARRISON AR72601 US > 870-365-8400 > 0519577690002 > 00002 > > > "Andrew J. Kelly" wrote: > >> Now you have lost me even more<g>. Are you saying that a data row spans >> multiple rows in the file? Can you just post a sample of the actual file? >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "Bigal" wrote in message >> >> > Dont know the terminology so I will explain this the best I can. Every >> > field >> > is in a fixed postion. See example >> > >> > Field Type RG Line Column >> > Width >> > Print Width >> > ----------------------------------------------------------------------------------------- >> > :WALM816Btest: Literal Text 0 1 1 0 >> > 10 >> > >> > 0000 Literal Text 0 2 1 >> > 1 >> > 4 >> > BHT02 AlphaNumeric 0 2 5 2 >> > 2 >> > >> > BHT01 AlphaNumeric 0 2 7 4 >> > 4 >> > BHT03 AlphaNumeric 0 2 11 50 >> > 50 >> > >> > >> > "Andrew J. Kelly" wrote: >> > >> >> It is either fixed length or it is not. If all the columns are not >> >> the >> >> same >> >> width in all the rows for each column it is not a fixed width. Or the >> >> file >> >> needs manual work to get it there. Without delimiters you need some >> >> way >> >> to >> >> determine where one column starts and one ends. >> >> >> >> -- >> >> Andrew J. Kelly SQL MVP >> >> >> >> >> >> "Bigal" wrote in message >> >> >> >> > Thanks for your reply. The file is fixed but the data is not >> >> > properly >> >> > lined >> >> > up to import it the way it is. >> >> > >> >> > "Andrew J. Kelly" wrote: >> >> > >> >> >> Just use the DTS import wizard and follow the prompts. You say it >> >> >> is >> >> >> not >> >> >> delimited so it must be fixed width which the wizard usually >> >> >> figures >> >> >> out >> >> >> on >> >> >> it's own. Go to tools - Import in EM. >> >> >> >> >> >> -- >> >> >> Andrew J. Kelly SQL MVP >> >> >> >> >> >> >> >> >> "Bigal" wrote in message >> >> >> >> >> >> > Hello, >> >> >> > >> >> >> > I need to import data from a flat file into a sql server 2000 >> >> >> > database. >> >> >> > The >> >> >> > Fate file has no delimiters. I have the specs for this file so I >> >> >> > do >> >> >> > knowthe >> >> >> > layout of this file. Can somebody point me in the rigvht >> >> >> > direction >> >> >> > to >> >> >> > accomplish this. >> >> >> > >> >> >> > Thanks >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> Stay informed about: Flat File import

David Henson

External

Since: Jan 03, 2006
Posts: 1

(Msg. 10) Posted: Tue Jan 03, 2006 9:18 am Post subject: Re: Flat File import [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

This is an EDI file. DTS will in no way handle this file. You will have to write something in C#, purchase a third party app to parse this file(5K and up), or pay a service provider to translate it into something database oriented. Good luck...this is a BIG job. -Dave Henson dhenson.TakeThisOut@certifiednetworks.com "Bigal" wrote in message > Hello, > > I need to import data from a flat file into a sql server 2000 database. > The > Fate file has no delimiters. I have the specs for this file so I do > knowthe > layout of this file. Can somebody point me in the rigvht direction to > accomplish this. > > Thanks >> Stay informed about: Flat File import

mattb

External

Since: Nov 09, 2005
Posts: 2

(Msg. 11) Posted: Fri Jan 06, 2006 12:01 pm Post subject: RE: Flat File import [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

"Bigal" wrote: > Hello, > > I need to import data from a flat file into a sql server 2000 database. The > Fate file has no delimiters. I have the specs for this file so I do knowthe > layout of this file. Can somebody point me in the rigvht direction to > accomplish this. > > Thanks Looks like you will have to manually line it up - us a text editor or excel, then import it into sql. May take 6 hrs, but certainly doable. >> Stay informed about: Flat File import

Bigal

External

Since: Dec 27, 2005
Posts: 6

(Msg. 12) Posted: Wed Jan 18, 2006 12:38 pm Post subject: RE: Flat File import [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)

Thanks 4 all the help. I ended up just writing a VBscript using the FileSystem object and string functions to parse it into a regular csv file. Finally works the way I wanted. Thanks again >> Stay informed about: Flat File import

Display posts from previous:

Related Topics:

Import flat file with no column names into sql server 2005 - Ok Gurus, Here is an interesting situation, I have a coworker that has a flat file with no column headers that needs to be imported or appended into a table in SQL Server 2005. Since the file has no column names each time during the import it inserts..

Flat File - Different row types - still confused - I'm trying to process header/detail lines in a flat file, something I've seen a few examples of, but still have some questions. I've seen Allan Mitchell's comments and an example on sqlis.com (thanks for the fine examples!), and tried an example from...

SSIS - Flat File Location - I have a SSIS package that imports data from a flat file into a table. The file is in a network share. The package itself is in my local machine. The sql server itself is running on my local machine. This will be changed later to a server. The packag...

Flat file src error-"The value could not be converted beca.. - Hello, I have a flat file source with ragged right format. It has three columns. My package fails when the last column has null values. It says "The value could not be converted because of a potential loss of data". I tried lot chang...

Flat file text delimiter - I am using SQL2005 management studio to export the data from a table to a flat file. If I include a text delimiter it adds it to every column, i have checked the columns with the edit transforms button and some of them are ints. Is there a setting..

	Database Forums (Home) -> DTS	All times are: Pacific Time (US & Canada)
Page 1 of 1

You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum