How to write a preliminary dbf-table structure with many fields before import csv 10

[german12]

I have a big *.csv File.
The first two lines show me a lot of Field-Names and that looks like this:

"Kontonummer""Bankleitzahl""IBAN""BIC""Betrag""Buchungstext""Betrag - Währung""Buchungstag""Begünstigter/Absender - Bankleitzahl""Begünstigter/Absender - Kontonummer""Begünstigter/Absender - Name""Internet""Kategorie""Kommentar""Kostenstelle""Laufende Nummer""Marker""Originalbetrag""Originalbetrag - Währung""Primanota""Saldo""Saldo - Währung""Storno""Storno - Originalbetrag""Splittbuchung - Auftraggeber / Name""Splittbuchung - Kategorie""Splittbuchung - Kostenstelle""Splittbuchung - Originalbetrag""Splittbuchung - Unterkategorie""Splittbuchung - Verwendungszweckzeile 1""Textschlüssel""Unterkategorie""Verwendungszweckzeile 1""Verwendungszweckzeile 2""Verwendungszweckzeile 3""Verwendungszweckzeile 4""Verwendungszweckzeile 5""Verwendungszweckzeile 6""Verwendungszweckzeile 7""Verwendungszweckzeile 8""Verwendungszweckzeile 9""Verwendungszweckzeile 10""Verwendungszweckzeile 11""Verwendungszweckzeile 12""Verwendungszweckzeile 13""Verwendungszweckzeile 14""Wertstellungstag""Steuersatz""SteuersatzWaehr""Steuerbetrag""SteuerbetragWaehr""Fibu-Nr.""Splittbuchung - Steuersatz""Splittbuchung - SteuersatzWaehr""Splittbuchung - Steuerbetrag""Splittbuchung - SteuerbetragWaehr""Splittbuchung - Fibu-Nr.""Abweichender Auftraggeber""Ende zu Ende Referenz""Kundenreferenz""Mandatsreferenz""Purpose Code""Rückgabegrund""Rückgabegrund (Code)""Entgelt (fremd)""Entgelt (fremd) Währung""Entgelt (eigen)""Entgelt (eigen) Währung""Zinskompensationsbetrag""Zinskompensationsbetrag Währung""Ursprungsbetrag""Ursprungsbetrag Währung""Gläubiger-Identifikation""Soll""Haben"

Of course it would be possible to create a *.dbf-File, and then import the *.csv-file - however that means to define each field with its character and field-length.

My question:

When I am too lazy to write all the above fields into the *.dbf-structure manually - would there be a possibility to do this by code (create table....) and let the code create first a preliminary table-structure?
The field-length and eventually their character could perhaps changed later - but as I know that the majority are character-fields and let the field-length be as long as their header - I can imagine that
this is easier to correct later instead of writing the whole table-structure manually.

Any idea for this sample?

Thanks
Klaus




Peace worldwide - it starts here...

 

[atlopes]

Klaus,

Set the .NullValue property to .NULL., in which case empty values from the CSV file will be treated as empty values in the resulting cursor. It does not affect the determination of the type of columns.

Will this make things work for you?

 

[Chris Miller]

Or the other way around, use NVL(column,'') to change each .NULL. to an empty string.

Also, don't alltrim each value or you will have no space separation of words in some cases. Add all text together and use the REDUCE function from foxtools.fll (See in Home, als described in foxtools.chm)

Chriss

 

[Chris Miller]

Another thing: If a short text is split into 14 char fields, I would expect all of them being c(256), I guess that only the first two are 142 and 140 is due to detection of necessary widths.

Anyway, if I'd split a text into several char fields to avoid a memo field because of CSV shortcomings of VFP, then I would perhaps not care to split in the middle of words, too, because I'd recombine the text simply by adding all the parts together, without ALLTRIM, and then just do a final RTRIM.

Just a thought. The problem differs when you need to put together multiple lines of text into a single line, but as said REDUCE then is a fine function, it reduces any multiple spaces to one and acts in all of a string, not just at the start and end.

Chriss

 

[german12]

Atlopes and Chriss,

Atlopes

Yes - that works perfect!

Chriss

As I did not know how to open a *.chm-file (how can I?) (I never had used foxtools before - I searched for a website - and found it:

https://www.foxtools.com/foxtoolsfll-help-file

and that is really a very nice function:

REDUCE(<ExpC1>, <ExpC2>)
Returns: Character
Description: Removes repetitive values in a string. Usually used to remove
groups of spaces in a string and replace it with one space.
<ExpC1> - Character string to change
<ExpC2> - Characters to search for
Example: ? REDUCE("This is lots of spaces", " ")
(returns: This is lots of spaces)
? REDUCE("Repeat characters AAAA and delete", "A")
(returns: Repe t ch r cters nd delete)


..and that is of course much better than building a long string with alltrim-functions.

Thank you both again.
Klaus


Peace worldwide - it starts here...

 

[Chris Miller]

chm is the help file format. Double clicking a chm opens it.

Chriss

 

[Mike Lewis]

I never had used foxtools before - I searched for a website - and found it:

https://www.foxtools.com/foxtoolsfll-help-file

You didn't need to search the web for it. The Foxtools Help file is already present in your VFP directory (named FOXTOOLS.CHM).

It is worth spending ten minutes perusing this file. You'll find all sorts of useful functions (although many of them have now been incorporated into the main VFP language).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads