Append from ... CSV (without field names)

[csr]

When I use the command APPEND FROM ... CSV there is an assumption that the first record contains field names. I would prefer to do this without involving field names at all. There doesn't appear to be any option for this. Is that true ?

Don

 

[Mike Lewis]

Hi Don,

This is easy. Just use APPEND FROM ... DELIMITED WITH ,

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[dgrewe]

Not without writing a quick low level file program to strip off the first line.
Or Importing the file then deleteing the first row.


David W. Grewe Dave

 

[csr]

Mike: I was using APPEND FROM ... DELIMITED but I had a problem with that. It seems that the MEMO field which I was putting into a 250 character field would spill over into additional fields if it contained more than 250 characters. So, I tried putting in the keyword CSV and that corrected the problem.


Don

 

[csr]

Mike: Also ... I just used DELIMITED in my command and did not include WITH , .... as you have indicated. I thought the comma was the default. I will try it with the WITH clause to see what happens.


Don

 

[csr]

Well, now I used ...

APPEND FROM ... DELIMITED WITH , WITH CHARACTER "

and it still causes the MEMO field data to overflow into subsequent fields when the size of the memo field exceeds 250 characters. I am guessing that there is some sort of issue with commas and double quotes being found within the memo field data. I do not have any control over the characters being used as delmiters in the source data. Any thoughts ?


Don

 

[csr]

I have discovered the culprit ! It has nothing to do with the 250 characters. That works fine. The problem lies within the memo field data. When the memo field data contains a pair of double quotes ("") the data following those double quotes is deposited in the next available field during the append. Any thoughts on this ?


Don

 

[csr]

Well, in the absence of any further ideas ... I will put things back to APPEND FROM ... CSV. Other than the fact that it ignores the first record, that works fine. I will check back periodically to see if anyone had a brainstorm.
Thanks for your efforts to help.


Don

 

[Stella740pl]

Just use FOR clause when appending the file - think of some condition that would be true for the regular data but not for the first line containing the field names.

Say, something like this:

APPEND FROM ... CSV FOR ALLTRIM(FirstField)#"FirstField"

 

[Mike Lewis]

Don,

This is entirely off the top of my head, and not tested ...

Before doing the APPEND, do something like this:

lcContents = FILETOSTR("MyFile.CSV")
lcContents = ;
  SUBSTR(lcContents, AT(CHR(13)+CHR(10), lcContents)+2)
STRTOFILE(lcContents, "NewFile.CSV")

You can then do the APPEND from NewFile.CSV, using TYPE CSV.

This assumes that the lines in the original text file end in CR + LF (in that order). Some text files just use CR, others just LF. You'll have to make an adustment to the above code if necessary.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[csr]

I think these latest thoughts have my original problem turned around. I am trying to solve this problem ...

The APPEND FROM ... CSV command automatically omits the first record in the append process. I am looking for some means of getting around this. In other words, I do not wish to omit that first record ... I want to include it as a valid data record.

That is the reason for trying to use APPEND FROM ... DELIMITED. That command does not omit the first record.
However, in trying to use that command I have found that it chokes on the MEMO field data when it encounters double quotes within the data. I unfortunately have no control over any aspect of the source file.



Don

 

[csr]

However, Mike's response has given me an idea ...


I may be able to use that same concept to ADD an additional record as the first record of the file. Then the command with the CSV keyword will automatically skip that dummy record.

My purpose in all of this is to avoid having to depend upon users to remember to specify that the data dump file is to contain a first record with field names. If they would forget, we would lose a data record and probably not know it.

So, that latest thought might give me something to work on.

Thanks again.



Don

 

[Mike Lewis]

Don,

I do not wish to omit that first record ... I want to include it as a valid data record.

I should have realised that.

However, you should be able to use a similar technique to do the opposite. Something like this perhaps:

SELECT TheTable
lnX = ""
FOR lnI = 1 TO FCOUNT()
  lnX = lnX + FIELD(lnI) + ","
ENDFOR
lnX = LEFT(lnX, LEN(lnX)-1)  && remove final comma

lcContents = FILETOSTR("MyFile.CSV")
lcContents = lnX + CHR(13) + CHR(10) + lcContents
STRTOFILE(lcContents, "NewFile.CSV")

Again, this is totally off the cuff, without any testing.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk