reading a flat file.... - comp.databases.mysql

Hi all, I have a flat(text) file of record length 320. I want to insert the data into a table. There are no delimiters. example: i want to insert the data from 1 to 9 as empcode, 10 to 14 as deptcode, 15 to 55 as empname, 150 to 156 as empbasic. (the numbers given are the character positions in the file) (as a beginner i am using awk to split the file and loading it into mysql table) Thank you in advance.

nag wrote: > Hi all, > > I have a flat(text) file of record length 320. I want to insert the > data into a table. There are no delimiters. > > example: > i want to insert the data > from 1 to 9 as empcode, > 10 to 14 as deptcode, > 15 to 55 as empname, > 150 to 156 as empbasic. > (the numbers given are the character positions in the file) > > (as a beginner i am using awk to split the file and loading it into > mysql table) LOAD DATA INFILE apparently wasn't designed to work with fixed-length fields. However, there are some user contributed comments towards the bottom of this page which might prove helpful: http://dev.mysql.com/doc/refman/5.0/en/load-data.html Other than that, I wonder if it would be easiest to create a table which has one column of type VARCHAR(...) wide enough to hold one entire line of data, import everything into that table, then do something like this (assuming that your imported data is kept in a table named "tmp_data" with one column "row"): INSERT INTO the_real_table (empcode, deptcode, empname, empbasic) SELECT substr(row,1,9) AS empcode, substr(row,10,5) AS deptcode, -- etc. substr(row,150,7) AS empbasic FROM tmp_data; You get the idea?

On 10 Sep, 16:04, Robert Hairgrove <rhairgr...@bigfoot.com> wrote: > nag wrote: > > Hi all, > > > I have a flat(text) file of record length 320. I want to insert the > > data into a table. There are no delimiters. > > > example: > > i want to insert the data > > from 1 to 9 as empcode, > > =A0 =A0 =A0 =A0 10 to 14 as deptcode, > > =A0 =A0 =A0 =A0 15 to 55 as empname, > > =A0 =A0 =A0 =A0 =A0150 to 156 as empbasic. > > =A0(the numbers given are the character positions in the file) > > > (as a beginner i am using awk to split the file and loading it into > > mysql table) > > LOAD DATA INFILE apparently wasn't designed to work with fixed-length > fields. It was, but the basic design isn't really flexible enough. > However, there are some user contributed comments towards the > bottom of this page which might prove helpful Indeed I have used the referenced methods many times to perform this task. > > http://dev.mysql.com/doc/refman/5.0/en/load-data.html > > Other than that, I wonder if it would be easiest to create a table which > has one column of type VARCHAR(...) wide enough to hold one entire line > of data, import everything into that table, then do something like this > (assuming that your imported data is kept in a table named "tmp_data" > with one column "row"): > > INSERT INTO the_real_table (empcode, deptcode, empname, empbasic) > SELECT substr(row,1,9) AS empcode, substr(row,10,5) AS deptcode, > -- etc. > substr(row,150,7) AS empbasic > FROM tmp_data; This is really no different from the user comments, except that you are inserting an extra step.

On Sep 10, 8:04 pm, Robert Hairgrove <rhairgr...@bigfoot.com> wrote: > nag wrote: > > Hi all, > > > I have a flat(text) file of record length 320. I want to insert the > > data into a table. There are no delimiters. > > > example: > > i want to insert the data > > from 1 to 9 as empcode, > > 10 to 14 as deptcode, > > 15 to 55 as empname, > > 150 to 156 as empbasic. > > (the numbers given are the character positions in the file) > > > (as a beginner i am using awk to split the file and loading it into > > mysql table) > > LOAD DATA INFILE apparently wasn't designed to work with fixed-length > fields. However, there are some user contributed comments towards the > bottom of this page which might prove helpful: > > http://dev.mysql.com/doc/refman/5.0/en/load-data.html > > Other than that, I wonder if it would be easiest to create a table which > has one column of type VARCHAR(...) wide enough to hold one entire line > of data, import everything into that table, then do something like this > (assuming that your imported data is kept in a table named "tmp_data" > with one column "row"): > > INSERT INTO the_real_table (empcode, deptcode, empname, empbasic) > SELECT substr(row,1,9) AS empcode, substr(row,10,5) AS deptcode, > -- etc. > substr(row,150,7) AS empbasic > FROM tmp_data; > > You get the idea? Nice idea....thank you very much, but the problem of creating a temp db with the entire records is place consuming.

On 10 Sep, 16:22, nag <visit...@gmail.com> wrote: > On Sep 10, 8:04 pm, Robert Hairgrove <rhairgr...@bigfoot.com> wrote: > > > > > > > nag wrote: > > > Hi all, > > > > I have a flat(text) file of record length 320. I want to insert the > > > data into a table. There are no delimiters. > > > > example: > > > i want to insert the data > > > from 1 to 9 as empcode, > > > =A0 =A0 =A0 =A0 10 to 14 as deptcode, > > > =A0 =A0 =A0 =A0 15 to 55 as empname, > > > =A0 =A0 =A0 =A0 =A0150 to 156 as empbasic. > > > =A0(the numbers given are the character positions in the file) > > > > (as a beginner i am using awk to split the file and loading it into > > > mysql table) > > > LOAD DATA INFILE apparently wasn't designed to work with fixed-length > > fields. However, there are some user contributed comments towards the > > bottom of this page which might prove helpful: > > >http://dev.mysql.com/doc/refman/5.0/en/load-data.html > > > Other than that, I wonder if it would be easiest to create a table whic= h > > has one column of type VARCHAR(...) wide enough to hold one entire line > > of data, import everything into that table, then do something like this > > (assuming that your imported data is kept in a table named "tmp_data" > > with one column "row"): > > > INSERT INTO the_real_table (empcode, deptcode, empname, empbasic) > > SELECT substr(row,1,9) AS empcode, substr(row,10,5) AS deptcode, > > -- etc. > > substr(row,150,7) AS empbasic > > FROM tmp_data; > > > You get the idea? > > Nice idea....thank you very much, > but the problem of creating a temp db with the entire records is place > consuming. As I pointed out, you don't need to do this. The user comment Posted by Ryan Neve on July 18 2008 7:16pm works perfectly as a method: To load a text file with fixed width columns, I used the form: LOAD DATA LOCAL INFILE '<file name>' INTO TABLE <table> (@var1) SET Date=3Dstr_to_date(SUBSTR(@var1,3,10),'%m/%d/%Y'), Time=3DSUBSTR(@var1,14,8), WindVelocity=3DSUBSTR(@var1,26,5), WindDirection=3DSUBSTR(@var1,33,3), WindCompass=3DSUBSTR(@var1,38,3), WindNorth=3DSUBSTR(@var1,43,6), WindEast=3DSUBSTR(@var1,51,6), WindSamples=3DSUBSTR(@var1,61,4);

On Sep 10, 8:39 pm, Captain Paralytic <paul_laut...@yahoo.com> wrote: > On 10 Sep, 16:22, nag <visit...@gmail.com> wrote: > > > > > On Sep 10, 8:04 pm, Robert Hairgrove <rhairgr...@bigfoot.com> wrote: > > > > nag wrote: > > > > Hi all, > > > > > I have a flat(text) file of record length 320. I want to insert the > > > > data into a table. There are no delimiters. > > > > > example: > > > > i want to insert the data > > > > from 1 to 9 as empcode, > > > > 10 to 14 as deptcode, > > > > 15 to 55 as empname, > > > > 150 to 156 as empbasic. > > > > (the numbers given are the character positions in the file) > > > > > (as a beginner i am using awk to split the file and loading it into > > > > mysql table) > > > > LOAD DATA INFILE apparently wasn't designed to work with fixed-length > > > fields. However, there are some user contributed comments towards the > > > bottom of this page which might prove helpful: > > > >http://dev.mysql.com/doc/refman/5.0/en/load-data.html > > > > Other than that, I wonder if it would be easiest to create a table which > > > has one column of type VARCHAR(...) wide enough to hold one entire line > > > of data, import everything into that table, then do something like this > > > (assuming that your imported data is kept in a table named "tmp_data" > > > with one column "row"): > > > > INSERT INTO the_real_table (empcode, deptcode, empname, empbasic) > > > SELECT substr(row,1,9) AS empcode, substr(row,10,5) AS deptcode, > > > -- etc. > > > substr(row,150,7) AS empbasic > > > FROM tmp_data; > > > > You get the idea? > > > Nice idea....thank you very much, > > but the problem of creating a temp db with the entire records is place > > consuming. > > As I pointed out, you don't need to do this. > > The user comment Posted by Ryan Neve on July 18 2008 7:16pm works > perfectly as a method: > > To load a text file with fixed width columns, I used the form: > LOAD DATA LOCAL INFILE '<file name>' INTO TABLE <table> > (@var1) > SET Date=str_to_date(SUBSTR(@var1,3,10),'%m/%d/%Y'), > Time=SUBSTR(@var1,14,8), > WindVelocity=SUBSTR(@var1,26,5), > WindDirection=SUBSTR(@var1,33,3), > WindCompass=SUBSTR(@var1,38,3), > WindNorth=SUBSTR(@var1,43,6), > WindEast=SUBSTR(@var1,51,6), > WindSamples=SUBSTR(@var1,61,4); my version (4.1.20) is not supporting syntax (@var1).

Op 11-09-10 18:35, nag schreef: > On Sep 10, 8:39 pm, Captain Paralytic<paul_laut...@yahoo.com> wrote: >> On 10 Sep, 16:22, nag<visit...@gmail.com> wrote: >> >> >> >>> On Sep 10, 8:04 pm, Robert Hairgrove<rhairgr...@bigfoot.com> wrote: >> >>>> nag wrote: >>>>> Hi all, >> >>>>> I have a flat(text) file of record length 320. I want to insert the >>>>> data into a table. There are no delimiters. >> >>>>> example: >>>>> i want to insert the data >>>>> from 1 to 9 as empcode, >>>>> 10 to 14 as deptcode, >>>>> 15 to 55 as empname, >>>>> 150 to 156 as empbasic. >>>>> (the numbers given are the character positions in the file) >> >>>>> (as a beginner i am using awk to split the file and loading it into >>>>> mysql table) >> >>>> LOAD DATA INFILE apparently wasn't designed to work with fixed-length >>>> fields. However, there are some user contributed comments towards the >>>> bottom of this page which might prove helpful: >> >>>> http://dev.mysql.com/doc/refman/5.0/en/load-data.html >> >>>> Other than that, I wonder if it would be easiest to create a table which >>>> has one column of type VARCHAR(...) wide enough to hold one entire line >>>> of data, import everything into that table, then do something like this >>>> (assuming that your imported data is kept in a table named "tmp_data" >>>> with one column "row"): >> >>>> INSERT INTO the_real_table (empcode, deptcode, empname, empbasic) >>>> SELECT substr(row,1,9) AS empcode, substr(row,10,5) AS deptcode, >>>> -- etc. >>>> substr(row,150,7) AS empbasic >>>> FROM tmp_data; >> >>>> You get the idea? >> >>> Nice idea....thank you very much, >>> but the problem of creating a temp db with the entire records is place >>> consuming. >> >> As I pointed out, you don't need to do this. >> >> The user comment Posted by Ryan Neve on July 18 2008 7:16pm works >> perfectly as a method: >> >> To load a text file with fixed width columns, I used the form: >> LOAD DATA LOCAL INFILE '<file name>' INTO TABLE<table> >> (@var1) >> SET Date=str_to_date(SUBSTR(@var1,3,10),'%m/%d/%Y'), >> Time=SUBSTR(@var1,14,8), >> WindVelocity=SUBSTR(@var1,26,5), >> WindDirection=SUBSTR(@var1,33,3), >> WindCompass=SUBSTR(@var1,38,3), >> WindNorth=SUBSTR(@var1,43,6), >> WindEast=SUBSTR(@var1,51,6), >> WindSamples=SUBSTR(@var1,61,4); > > > my version (4.1.20) is not supporting syntax (@var1). than you have 2 choices... - update to MySql5.1 (or newer) - keep using you awk-file to import this data... -- Luuk