Converting CSV file to FOXPRO

i need to convert my csv data to foxpro table...kindly help for example below:

"ALB-ME","44.6","G/L","ALP-ME","89.76","U/L","ALT/-ME","52.73","U/L"

With only three columns as shown "ALB-ME"-desc1,"44.6"-desc2,"G/L"-desc3 . All the data should be rows.

Kindly assist me
nyangemwasi mwasiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
If you need just 3 columns then your CSV file must contain more lines (and header line containing column names):

"desc1", "desc2", "desc3"
"ALB-ME","44.6","G/L"
"ALP-ME","89.76","U/L"
"ALT/-ME","52.73","U/L"

In such case you have to create appropriate table, e.g.

CREATE TABLE MyRawCSV (desc1 char(20), desc2 char(20), desc3 char(20))

and then append the CSV:

APPEND FROM  <YourCSVFileName> TYPE CSV

If you use
APPEND FROM  <YourCSVFileName> TYPE DELIMITED
then the CSV file does not need to contain the header row.

The rest of work is to convert the desc2 column to numeric data type. If you are sure the data in CSV are numeric then you may define the table with numeric column directly:

CREATE TABLE MyRawCSV (desc1 char(20), desc2 numeric(12,3), desc3 char(20))

If your CSV file contains just one line  ("ALB-ME","44.6","G/L","ALP-ME","89.76","U/L","ALT/-ME","52.73","U/L") then you need to process it by a short program or by appropriate tool to create a correct CSV.
0
nyangemwasi mwasiAuthor Commented:
It didn't work cause with this csv file it's just one line..thank you though
0
pcelbaCommented:
OK, it was expected.

What's the problem to insert CRLFs to split one single line into the format accepted by your data structure?
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

nyangemwasi mwasiAuthor Commented:
The problem is, this file is dumped from a lab machine. And it is not a one file. It occurs as many times. The users may not able to change the structure of the csv file before my program picks it for processing. Once more thanks for your effort.
0
pcelbaCommented:
OK, then then the short program which replaces each the 3rd comma by CRLF should help:
lcLine = FILETOSTR(<<YourCSVfile>>)
*lcLine = ["ALB-ME","44.6","G/L","ALP-ME","89.76","U/L","ALT/-ME","52.73","U/L"]
lnComma = 3

lnPos = AT(',', lcLine, lnComma)

DO WHILE lnPos > 0
  lcLine = STUFF(lcLine, lnPos, 1, CHR(13)+CHR(10))
  lnComma = lnComma + 2
  lnPos = AT(',', lcLine, lnComma)
ENDDO

= STRTOFILE(lcLine, <<YourNewCSVFile>>)

*? lcLine

Open in new window

Now you have a new CSV file which is suitable to import to your 3 columns table.
Lines commented out were used for debugging.
0
nyangemwasi mwasiAuthor Commented:
Thank you so much for the starter. Though the results return a small chunk of the total csv. I will attach a file for this purpose. Thank you so much.
results.csv
0
pcelbaCommented:
OK, this is also easily doable. You should just remove the "signature" from the CSV file and/or process it separately.

The "signature" is this part:   "=""""","N","JAMESNYAGA",,,,"08/05/18","15",
The rest seems to be consistent.

What would you like to do with the signature? It contains some (possibly important) metadata like user name or date.
0
nyangemwasi mwasiAuthor Commented:
The signature is important.. Though i thought i will put in a variable then input it later into my table after separating it with the main csv file.

I actually did it by  separating the signature with this; substr(lcLine,45,len(lcLine)) assuming the signature consumes the first 45 lines by default.

i will appreciate if you show me the way forward.

Thank you
0
pcelbaCommented:
This code strips the signature and stores some values into variables:
CLEAR
lcLine = FILETOSTR('vf.csv')
*lcLine = ["ALB-ME","44.6","G/L","ALP-ME","89.76","U/L","ALT/-ME","52.73","U/L"]

lcSignature = LEFT(lcLine, AT(',', lcLine, 8))  && based on number of commas
lcLine = SUBSTR(lcLine, AT(',', lcLine, 8)+1)
lnComma = 3

lnPos = AT(',', lcLine, lnComma)

DO WHILE lnPos > 0
  lcLine = STUFF(lcLine, lnPos, 1, CHR(13)+CHR(10))
  lnComma = lnComma + 2
  lnPos = AT(',', lcLine, lnComma)
ENDDO

? lcSignature
? lcLine
= STRTOFILE(lcLine, 'vf2.csv')

*-- Extract info from the signature ...   THIS can work when the signature is in the given format only!!! Better approach would be to split it based on the commas.
lcUserName = STREXTRACT(lcSignature, [,"], [",], 2)
? lcUsername
lcDate = STREXTRACT(lcSignature, [,"], [",], 3)
? lcDate

*-- Now we have the correct CSV so import it to some table
CREATE TABLE MyRawCSV (desc1 char(20), desc2 numeric(16,7), desc3 char(20))

APPEND FROM 'vf2.csv' TYPE DELIMITED

BROWSE

Open in new window

But it is possible to write it better for sure
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nyangemwasi mwasiAuthor Commented:
This is superb. However the  output is less than the actual csv data. Example below:

"=""""","N","JAMESNYAGA",,,,"08/05/18","15","ALB-ME","44.6","G/L","ALP-ME","89.76","U/L","ALT/-ME","52.73","U/L","AST/-ME","48.39","U/L","CHOL-ME","4.211","MMOL/L","CREA-ME","95.87","UMOL/L","DBIL-ME","3.573","UMOL/L","GGT-ME","122.7","U/L","HDL-ME","0.7894","MMOL/L","LDL-ME","2.7593272","MMOL/L","TBIL-ME","11.27","UMOL/L","TOTA-ME","5.3344312","MMOL/L","TP-ME","66.92","G/L","TRG-ME","1.457","MMOL/L","UREA-ME","2.42","UMOL/L","G/L","TRC-ME","3.457","MMOL/L","UREA-MB","8.42","UMOL/L"

From here am only getting 15 records. Is there a way i can extract everything?

Thank you once more.
0
pcelbaCommented:
The result.csv file posted earlier contains just signature + 15 records.  The CSV presented in above string contains more records but it also seems to be corrupted.

Look at the  ...  "UREA-ME","2.42","UMOL/L","G/L","TRC-ME"  ...  near to the end. Are the two units of measure OK?

The code uses vf.csv as the input file and you should use whatever name you need. The best is if you store the name into a variable.
0
nyangemwasi mwasiAuthor Commented:
Sorry, sometimes the lab results come up more than 15 records.i have tried running it but it limits to 15 records. Can it the code be enlarged to accomodate more.

"=""""","N","JAMESNYAGA",,,,"08/05/18","15","ALB-ME","44.6","G/L","ALP-ME","89.76","U/L","ALT/-ME","52.73","U/L","AST/-ME","48.39","U/L","CHOL-ME","4.211","MMOL/L","CREA-ME","95.87","UMOL/L","DBIL-ME","3.573","UMOL/L","GGT-ME","122.7","U/L","HDL-ME","0.7894","MMOL/L","LDL-ME","2.7593272","MMOL/L","TBIL-ME","11.27","UMOL/L","TOTA-ME","5.3344312","MMOL/L","TP-ME","66.92","G/L","TRG-ME","1.457","MMOL/L","UREA-ME","2.42","UMOL/L"
0
nyangemwasi mwasiAuthor Commented:
Will appreciate the effort.
0
pcelbaCommented:
The code can process as many result as you provide BUT it expects triplets - name, value, unit. Once the machine exports two units instead of one then it will need some intelligence to process such data.

BTW, what is the meaning of the last number in the signature?
0
nyangemwasi mwasiAuthor Commented:
The output file should be only name,value,unit. There is no provision for two units. The last number is file number.Much appreciated.
0
pcelbaCommented:
OK, then the code should work for any number of triplets in the "one line CSV".
0
nyangemwasi mwasiAuthor Commented:
True but you can see that the CSV file if it has lots of data it tend to creates several lines.
0
pcelbaCommented:
Do you have such file example?
0
nyangemwasi mwasiAuthor Commented:
Mr pcelba, i have attached the zip file and the output for your consideration. Thank you
0
pcelbaCommented:
No ZIP file is available for me here.
0
nyangemwasi mwasiAuthor Commented:
0
pcelbaCommented:
The conversion seems to be correct in the file attached.

So what's wrong with it?
0
nyangemwasi mwasiAuthor Commented:
Thanks sir. I didn't look at the output file well. it was just a silly view i had. This was an exemplary job. i could not thank you more. Well appreciated. And by the way i do like your contributions. Lesson learnt. Thanks
0
pcelbaCommented:
Author just forgot to close the question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FoxPro

From novice to tech pro — start learning today.