Multiple text file import with arrays? 1

[DocBus]

I have a problem that I am sure I am not the first to come across. My data is generated from Mumps into delimited text files. I cannot change that. I have over 100 tables with 50 variables in each. VFP9 can import text files with the import wizard but that will force me to repeat the process 100 times. My text files are delimted (TAB) and the field names are in the first line. I can get the fieldd names out of the text file in VFP, but cannot use the subsequent arrays to generate a tabel in SQL with the array values. Just trying to create a table with one array only does not work. Please see my little (simple I admit and probably not so elegant) program below.

My question - Is there any way to generate VFP tables from multitude of text files without first having to create tables by hand and then use the APPEND from function. With 100s of tables this becomes quite tedious and I would like to automate the process.

I'll appreciate any help!!!!! THANKS

Cobus

CODE snippet>>>>

LPARAMETERS cFileName

IF EMPTY(cFileName)
    cFileName = GETFILE()
ENDIF


LOCAL aDataFile[1]

cFileData = FILETOSTR(cFileName)
nLinesToProcess = ALINES(aDataFile, cFileData)

STORE GETWORDCOUNT(aDataFile[1], CHR(9)) TO nFieldnames
*** create array
DIMENSION CFieldnames [nFieldnames]

*** First Field
STORE SUBSTR(aDataFile[1], 1, ATC(CHR(9),aDataFile[1])) TO cFieldnames[1]

*** Following Fields
FOR nTabs = 2 TO (nFieldnames - 1)

	STORE  STREXTRACT(aDataFile[1], CHR(9), CHR(9), ntabs-1 ) TO cFieldnames[nTabs]

ENDFOR

[COLOR=red]*** THIS iS WHERE IT GOES WRONG :-( even with just one array value

CREATE TABLE logs.dbf;
	( cFieldnames[1] c(20)) [/color]

 

[Stefan5627]

Hello DocBus,

Try creating the table using macrosubstitution e.g.:

lcCreate = "create table logs.dbf ( " + cFieldnames[1] + " c(20) ) "

&lcCreate

hth,

Stefan

 

[TamarGranor]

Check out TYPE CSV, so you don't have to parse out the first line.

Tamar

 

[wgcs]

I know in another thread you mentioned that the file is definitely and unchangeably Tab-delimited as it is produced by Mumps, so CSV doesn't (directly) help.

Depending on the size of the text tables (ie, if they are each smaller than 16MB), it might not be unreasonable to convert the tab-delimited to CSV through a couple commands:

(given that lcTextFile is the fullpath to the text file)

lcTabDelim = filetostr(lcTextFile)

* Convert all tabs to quote comma quote
lcCsv = strtran( lcTabDelim, chr(8), '","' ) 

* Add quote at beginning and end of every line:
*  (this assumes all lines end with CRLF)
lcCsv = '"' + strtran( lcTabDelim, chr(13)+chr(10), '"'+chr(13)+chr(10)+'"' ) + '"'

* Save into new file, and import:
lcCsvFile = ForceExt(lcTextFile,'CSV')
StrToFile(lcCsv, lcCsvFile )
IMPORT FROM (lcCsvFile) TYPE CSV

- Bill

Get the best answers to your questions -- See FAQ481-4875.

 

[DocBus]

Bill,

Thanks for the idea!

I have two problems - the files are greater than 100MB and contain commas :-(. But what I can try is the to transfer use strtrans to first convert the commas to asterikses or something like that. Any ideas on how to chop the files into chunks smaller than 16MB?

Regards,

Cobus

 

[wgcs]

Before FILETOSTR() existed, we had to use FOPEN, FREAD and FGETS... so it isn't too much harder to do it that way:

for lnI = 1 to lnCnt
  lnInFile = fOpen( laFiles[lnI,1] )
  lnOutFile = fcreate( forceExt(laFiles[lnI,1],'csv') )
  do while not FEOF(lnInFile)
    lcTabDelim = fgets(lnInFile)

    * Convert all tabs to quote comma quote
    lcCsv = '"' + strtran( lcTabDelim, chr(8), '","' ) + '"'

    fputs(lnOutFile,lcCsv)
  enddo
  fclose(lnInFile)
  fclose(lnOutFile)
endfor

As for the commas, they shouldn't be a problem: Follow the logic in my posts above, and you'll notice that tab characters are converted to Quote, Comma, Quote... any commas in the original values will be enclosed in quotes:

That is, the line:
data(tab)some,data(tab)more data

is converted to:
data","some,data","more data

Then the leading and ending quotes are added:
"data","some,data","more data"

(In this post, I combined the two steps into one command, since we don't have to process CRLF's, as in my first post)

I can't predict how fast this will be when operating on 100MB files, but the automation probably will make up for any delays in processing, since you won't have to intervene in the middle of the process.


- Bill

Get the best answers to your questions -- See FAQ481-4875.