Import comma delimited text file 
Author Message
 Import comma delimited text file

Using C55g. I have a comma delimited text file that I want to import
into a TPS file but I have no idea how to do this. Below is an example
of a single record in the file.

G1__00240030 , CWEXP2 ,293.00, US , EA , 20030505 , 20030505 , 20201231

There are 12 characters in the first field. What I want to do is get the
first four, second four, and third four characters in the first "field"
and put them into separate fields in a TPS file. Then I want to take all
the characters in the second and third "fields" and put them into two
separate TPS fields.  Disregard the fourth field. Get the fifth field
and disregard the remaining fields. In other words do the following.

Text Field 1 = G1__00240030
Text Field 2 =  CWEXP2      !Can be up to 20 characters
Text Field 3 = 293.00       !Can be up to 11 characters
Text Field 4 - Disregard
Text Field 5 = EA           !2 characters
Disregard remaining fields

TPS file structure
TPS field 1 = G1__
TPS field 2 = 0024
TPS field 3 = 0030
TPS field 4 = CWEXP2
TPS field 5 = 293.00
TPS field 6 = EA

I thought I could import the text file and create the TPS file. Then do
a conversion. But I don't know how to parse out the elements of the text
file and get them into the correct TPS fields.

Gary



Mon, 12 Dec 2005 10:03:40 GMT  
 Import comma delimited text file
1.  Import the file into the Data Dictionary with a DOS Driver.

2.  You will be presented with FIELD1, FIELD2, FIELD3...

CLEAR(MyFile:Record)
MyFile:Firstfour1 = SUB(FIELD1,1,4)
MyFile:Firstfour2 = SUB(FIELD1,5,4)
MyFile:Firstfour3 = SUB(FIELD1,1,4)
MyFile:SecondField = FIELD2
MyFile:ThirdField    = FIELD3
MyFile:FifthField    = FIELD5
ADD(MyTPSFile)


Quote:
> Using C55g. I have a comma delimited text file that I want to import
> into a TPS file but I have no idea how to do this. Below is an example
> of a single record in the file.

> G1__00240030 , CWEXP2 ,293.00, US , EA , 20030505 , 20030505 , 20201231

> There are 12 characters in the first field. What I want to do is get the
> first four, second four, and third four characters in the first "field"
> and put them into separate fields in a TPS file. Then I want to take all
> the characters in the second and third "fields" and put them into two
> separate TPS fields.  Disregard the fourth field. Get the fifth field
> and disregard the remaining fields. In other words do the following.

> Text Field 1 = G1__00240030
> Text Field 2 =  CWEXP2      !Can be up to 20 characters
> Text Field 3 = 293.00       !Can be up to 11 characters
> Text Field 4 - Disregard
> Text Field 5 = EA           !2 characters
> Disregard remaining fields

> TPS file structure
> TPS field 1 = G1__
> TPS field 2 = 0024
> TPS field 3 = 0030
> TPS field 4 = CWEXP2
> TPS field 5 = 293.00
> TPS field 6 = EA

> I thought I could import the text file and create the TPS file. Then do
> a conversion. But I don't know how to parse out the elements of the text
> file and get them into the correct TPS fields.

> Gary



Mon, 12 Dec 2005 10:25:44 GMT  
 Import comma delimited text file
Well... don't forget the prefix of your imported DOS file...


Quote:
> 1.  Import the file into the Data Dictionary with a DOS Driver.

> 2.  You will be presented with FIELD1, FIELD2, FIELD3...

> CLEAR(MyFile:Record)
> MyFile:Firstfour1 = SUB(FIELD1,1,4)
> MyFile:Firstfour2 = SUB(FIELD1,5,4)
> MyFile:Firstfour3 = SUB(FIELD1,1,4)
> MyFile:SecondField = FIELD2
> MyFile:ThirdField    = FIELD3
> MyFile:FifthField    = FIELD5
> ADD(MyTPSFile)



> > Using C55g. I have a comma delimited text file that I want to import
> > into a TPS file but I have no idea how to do this. Below is an example
> > of a single record in the file.

> > G1__00240030 , CWEXP2 ,293.00, US , EA , 20030505 , 20030505 , 20201231

> > There are 12 characters in the first field. What I want to do is get the
> > first four, second four, and third four characters in the first "field"
> > and put them into separate fields in a TPS file. Then I want to take all
> > the characters in the second and third "fields" and put them into two
> > separate TPS fields.  Disregard the fourth field. Get the fifth field
> > and disregard the remaining fields. In other words do the following.

> > Text Field 1 = G1__00240030
> > Text Field 2 =  CWEXP2      !Can be up to 20 characters
> > Text Field 3 = 293.00       !Can be up to 11 characters
> > Text Field 4 - Disregard
> > Text Field 5 = EA           !2 characters
> > Disregard remaining fields

> > TPS file structure
> > TPS field 1 = G1__
> > TPS field 2 = 0024
> > TPS field 3 = 0030
> > TPS field 4 = CWEXP2
> > TPS field 5 = 293.00
> > TPS field 6 = EA

> > I thought I could import the text file and create the TPS file. Then do
> > a conversion. But I don't know how to parse out the elements of the text
> > file and get them into the correct TPS fields.

> > Gary



Mon, 12 Dec 2005 10:26:38 GMT  
 Import comma delimited text file
TPS1 = Text[1:4]
TPS2 = Text[5:8]
TPS3 = Text{9:12]

--

Steve Parker

--------------------------------------------------
kb and download center at:
     www.par2.com
KB: http://www.par2.com/cws/c5launch.dll/FAQS/THEFAQS.exe.0
--------------------------------------------------

"The improbable, we do. The impossible takes a bit longer."


Quote:
> Using C55g. I have a comma delimited text file that I want to import
> into a TPS file but I have no idea how to do this. Below is an example
> of a single record in the file.

> G1__00240030 , CWEXP2 ,293.00, US , EA , 20030505 , 20030505 , 20201231

> There are 12 characters in the first field. What I want to do is get the
> first four, second four, and third four characters in the first "field"
> and put them into separate fields in a TPS file. Then I want to take all
> the characters in the second and third "fields" and put them into two
> separate TPS fields.  Disregard the fourth field. Get the fifth field
> and disregard the remaining fields. In other words do the following.

> Text Field 1 = G1__00240030
> Text Field 2 =  CWEXP2      !Can be up to 20 characters
> Text Field 3 = 293.00       !Can be up to 11 characters
> Text Field 4 - Disregard
> Text Field 5 = EA           !2 characters
> Disregard remaining fields

> TPS file structure
> TPS field 1 = G1__
> TPS field 2 = 0024
> TPS field 3 = 0030
> TPS field 4 = CWEXP2
> TPS field 5 = 293.00
> TPS field 6 = EA

> I thought I could import the text file and create the TPS file. Then do
> a conversion. But I don't know how to parse out the elements of the text
> file and get them into the correct TPS fields.

> Gary



Mon, 12 Dec 2005 10:26:39 GMT  
 Import comma delimited text file
Sorry...

Quote:
> > CLEAR(MyFile:Record)
> > MyFile:Firstfour1 = SUB(FIELD1,1,4)
> > MyFile:Firstfour2 = SUB(FIELD1,5,4)
> > MyFile:Firstfour3 = SUB(FIELD1,1,4)

      MyFile:Firstfour3 = SUB(FIELD1,9,4)       <==== Please change to
this...
Quote:
> > MyFile:SecondField = FIELD2
> > MyFile:ThirdField    = FIELD3
> > MyFile:FifthField    = FIELD5
> > ADD(MyTPSFile)



> > > Using C55g. I have a comma delimited text file that I want to import
> > > into a TPS file but I have no idea how to do this. Below is an example
> > > of a single record in the file.

> > > G1__00240030 , CWEXP2 ,293.00, US , EA , 20030505 , 20030505 ,
20201231

> > > There are 12 characters in the first field. What I want to do is get
the
> > > first four, second four, and third four characters in the first
"field"
> > > and put them into separate fields in a TPS file. Then I want to take
all
> > > the characters in the second and third "fields" and put them into two
> > > separate TPS fields.  Disregard the fourth field. Get the fifth field
> > > and disregard the remaining fields. In other words do the following.

> > > Text Field 1 = G1__00240030
> > > Text Field 2 =  CWEXP2      !Can be up to 20 characters
> > > Text Field 3 = 293.00       !Can be up to 11 characters
> > > Text Field 4 - Disregard
> > > Text Field 5 = EA           !2 characters
> > > Disregard remaining fields

> > > TPS file structure
> > > TPS field 1 = G1__
> > > TPS field 2 = 0024
> > > TPS field 3 = 0030
> > > TPS field 4 = CWEXP2
> > > TPS field 5 = 293.00
> > > TPS field 6 = EA

> > > I thought I could import the text file and create the TPS file. Then
do
> > > a conversion. But I don't know how to parse out the elements of the
text
> > > file and get them into the correct TPS fields.

> > > Gary



Mon, 12 Dec 2005 10:32:07 GMT  
 Import comma delimited text file
Hello Gary:

It looks like the text file doesn't have quotes for the string values. You
can use the BASIC file driver to read this comma delimited file and set the
driver string ALWAYSQUOTE=off.

Maurice


Quote:
> Using C55g. I have a comma delimited text file that I want to import
> into a TPS file but I have no idea how to do this. Below is an example
> of a single record in the file.

> G1__00240030 , CWEXP2 ,293.00, US , EA , 20030505 , 20030505 , 20201231

> There are 12 characters in the first field. What I want to do is get the
> first four, second four, and third four characters in the first "field"
> and put them into separate fields in a TPS file. Then I want to take all
> the characters in the second and third "fields" and put them into two
> separate TPS fields.  Disregard the fourth field. Get the fifth field
> and disregard the remaining fields. In other words do the following.

> Text Field 1 = G1__00240030
> Text Field 2 =  CWEXP2      !Can be up to 20 characters
> Text Field 3 = 293.00       !Can be up to 11 characters
> Text Field 4 - Disregard
> Text Field 5 = EA           !2 characters
> Disregard remaining fields

> TPS file structure
> TPS field 1 = G1__
> TPS field 2 = 0024
> TPS field 3 = 0030
> TPS field 4 = CWEXP2
> TPS field 5 = 293.00
> TPS field 6 = EA

> I thought I could import the text file and create the TPS file. Then do
> a conversion. But I don't know how to parse out the elements of the text
> file and get them into the correct TPS fields.

> Gary



Mon, 12 Dec 2005 10:37:15 GMT  
 Import comma delimited text file
If only importing and there are no quotes, the BASIC
driver will read it without the driver string (I found out
recently).

Steve Parker

--------------------------------------------------
kb and download center at:
     www.par2.com
KB: http://www.par2.com/cws/c5launch.dll/FAQS/THEFAQS.exe.0
--------------------------------------------------

"The improbable, we do. The impossible takes a bit longer."



Quote:
> Hello Gary:

> It looks like the text file doesn't have quotes for the string values. You
> can use the BASIC file driver to read this comma delimited file and set
the
> driver string ALWAYSQUOTE=off.

> Maurice



> > Using C55g. I have a comma delimited text file that I want to import
> > into a TPS file but I have no idea how to do this. Below is an example
> > of a single record in the file.

> > G1__00240030 , CWEXP2 ,293.00, US , EA , 20030505 , 20030505 , 20201231

> > There are 12 characters in the first field. What I want to do is get the
> > first four, second four, and third four characters in the first "field"
> > and put them into separate fields in a TPS file. Then I want to take all
> > the characters in the second and third "fields" and put them into two
> > separate TPS fields.  Disregard the fourth field. Get the fifth field
> > and disregard the remaining fields. In other words do the following.

> > Text Field 1 = G1__00240030
> > Text Field 2 =  CWEXP2      !Can be up to 20 characters
> > Text Field 3 = 293.00       !Can be up to 11 characters
> > Text Field 4 - Disregard
> > Text Field 5 = EA           !2 characters
> > Disregard remaining fields

> > TPS file structure
> > TPS field 1 = G1__
> > TPS field 2 = 0024
> > TPS field 3 = 0030
> > TPS field 4 = CWEXP2
> > TPS field 5 = 293.00
> > TPS field 6 = EA

> > I thought I could import the text file and create the TPS file. Then do
> > a conversion. But I don't know how to parse out the elements of the text
> > file and get them into the correct TPS fields.

> > Gary



Mon, 12 Dec 2005 11:09:44 GMT  
 Import comma delimited text file

Quote:
> If only importing and there are no quotes, the BASIC
> driver will read it without the driver string (I found out
> recently).

That's interesting since help says that the ALWAYSQUOTE driver string is
only for reading a file and has no effect when writing to the file.

Maurice



Mon, 12 Dec 2005 21:09:42 GMT  
 Import comma delimited text file
Hmmm. Recently I wrote a file with /ALWAYSQUOTE=off and
it did not write the quotes. When I was supplied with a
comma delimited file without quotes to read, the standard
driver read it correctly.

Obviously, one must test case-by-case.

Steve Parker

--------------------------------------------------
kb and download center at:
     www.par2.com
KB: http://www.par2.com/cws/c5launch.dll/FAQS/THEFAQS.exe.0
--------------------------------------------------

"The improbable, we do. The impossible takes a bit longer."



Quote:


> > If only importing and there are no quotes, the BASIC
> > driver will read it without the driver string (I found out
> > recently).

> That's interesting since help says that the ALWAYSQUOTE driver string is
> only for reading a file and has no effect when writing to the file.

> Maurice



Mon, 12 Dec 2005 21:20:14 GMT  
 Import comma delimited text file

Quote:
> Hmmm. Recently I wrote a file with /ALWAYSQUOTE=off and
> it did not write the quotes. When I was supplied with a
> comma delimited file without quotes to read, the standard
> driver read it correctly.

> Obviously, one must test case-by-case.

Sorry. Read the docs too fast. It will write quotes for the following
circumstances:

When the contents of a string field includes the comma or quote
character(s)...

Maurice



Mon, 12 Dec 2005 21:29:41 GMT  
 Import comma delimited text file
Yeah, that sounds familiar.

Steve Parker

--------------------------------------------------
kb and download center at:
     www.par2.com
KB: http://www.par2.com/cws/c5launch.dll/FAQS/THEFAQS.exe.0
--------------------------------------------------

"The improbable, we do. The impossible takes a bit longer."



Quote:


> > Hmmm. Recently I wrote a file with /ALWAYSQUOTE=off and
> > it did not write the quotes. When I was supplied with a
> > comma delimited file without quotes to read, the standard
> > driver read it correctly.

> > Obviously, one must test case-by-case.

> Sorry. Read the docs too fast. It will write quotes for the following
> circumstances:

> When the contents of a string field includes the comma or quote
> character(s)...

> Maurice



Mon, 12 Dec 2005 22:05:38 GMT  
 Import comma delimited text file
Thanks for the suggestions. I'll give them a try.

Gary



Tue, 13 Dec 2005 03:42:24 GMT  
 Import comma delimited text file
Gary,

Quote:
> G1__00240030 , CWEXP2 ,293.00, US , EA , 20030505 , 20030505 , 20201231

ImpFile   FILE,PRE(CSV),DRIVER('Basic','/ALWAYSQUOTE=off'),NAME(GLO:ImpFile)
Record      RECORD,PRE()
Field1           STRING(12)
Field2           STRING(20)
Field3           STRING(11)
Field4           STRING(2)
Field5           STRING(2)
Field6           STRING(8)    !These fields may be ommitted
Field7           STRING(8)    ! because they are not used
Field8           STRING(8)    ! added for commentary purpose
                 END
               END

TpsFile   FILE,PRE(TPS),DRIVER('TopSpeed'),CREATE,NAME(GLO:TpsFile)
                   !add keys if you need/want them
Record       RECORD,PRE()
Field1           STRING(4)
Field2           STRING(4)
Field3           STRING(4)
Field4           STRING(20)
Field5           DECIMAL(6,2)
Field6           STRING(2)
                 END
               END

 GLO:ImpFile = 'MyImportFileName'
 GLO:TpsFile = 'MyTpsFileName'
 OPEN(ImpFile)
 IF ErrorCode() .        !Not found
 OPEN(TpsFile)
 IF ErrorCode()          !Not found
   CREATE(TpsFile)
   OPEN(TpsFile)
 END
 SET(ImpFile)
 LOOP
    NEXT(ImpFile)
    IF ErrorCode() THEN BREAK .
    !
    TPS:Field1 = CSV:Field1[1:4]
    TPS:Field2 = CSV:Field1[5:8]
    TPS:Field3 = CSV:Field1[9:12]
    TPS:Field4 = CSV:Field2
    TPS:Field5 = CSV:Field3
    TPS:Field6 = CSV:Field5
    ADD(TpsFile)
    IF ErrorCode() .        !Something wrong
 END

--

Best regards,
Maarten
CDD3.1, C5EEb - ABC & C5.5.08EE
Certainly Clarion Developer

"I know what I know but I do not know what you don't know"



Tue, 13 Dec 2005 04:22:29 GMT  
 
 [ 13 post ] 

 Relevant Pages 

1. importing from a comma delimited file

2. VW code to read comma-delimited text files??

3. Fortran 77: read second number on a line in a comma delimited text file

4. Export Clarion .DAT files to ASCII comma delimited files

5. Export Clarion .DAT files to ASCII comma delimited files

6. getting fields NOT comma delimited with commas inside

7. AWK & Comma Delimited Text

8. Parsing Comma delimited files in J

9. matching records in a comma delimited file

10. Comma delimited file problem

11. 2.01 Comma delimited ASCII file

12. Comma delimited file?

 

 
Powered by phpBB® Forum Software