Appending CSV and not getting NULLs 1

[Wangta]

HI guys - i'm trying to append a CSV file that is created with excel (save as CSV - yes, I deleted the headers before saving). The append is working fine "Append from FILE.CSV type CSV" - however, i have numeric columns in the table that have NULL enabled, however, upon import, they are showing 0.0000 instead of NULL. I checked the csv file, and the actual value getting imported into that field/column is shown as not having a value - an example: 9.0234,,10.3211

This will show up in teh foxpro table as 9.0234,0.0000,10.3211

How do i fix this?

 

[Mike Lewis]

Just because you have enabled NULL support in the destination table, that doesn't mean that empty values in the CSV will automatically appear as NULLs.

The obvious solution would be to replace the zeroes with NULLs after the import:

REPLACE TheField WITH NULL FOR TheField = 0

The problem with that is that it will also replace the fields that you genuinely want to contain zero. The only way to solve that would be to go back to Excel and use some other method of depicting empty values.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Just to amplify my last remark ...

Suppose you know that the field in question can never contain negative values. Then, in Excel, you replace the empty cells with -1. After importing into VFP, you replace all the fields containing -1 with NULL.

Or, you could do that at the CSV level. In VFP, before the import, you read the CSV file into a variable (using FILETOSTR()); you then replace all instances of a double comma with comma - minus one - comma (using STRTRAN()); and write it back to the file (STRTOFILE()). Do the import, then replace all the fields containing -1 with NULL.

If you can't use negative numbers in that way (because they have a real meaning in the Excel file), then use some other "impossible" value instead.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

The problem is not only that a nullable field doesn't have .NULL. as their default value, VFP APPEND TYPE CSV also doesn't import strings null or even VFPs .NULL. as null, I even see rows with such content totally skipped from appending.

Actualöly Excel only has special handling of TRUE and FALSE (in other office language packs like German even words in that language WAHR and FALSCH which shows in that excel aligns them centered not right aligned as numbers or left aligned as text.

I assume you really just have blank cells resulting in nothing between two commas in the CSV file as read in a text editor.

The simplest way to read this CSV in is using a staging or import table with all text fields and converting from there. It then is not just a matter of altering the table to get blank text fields to NULL, not even the words "NULL" or ".NULL." convert to NULL, they convert to 0. But obviously, EVAL(".NULL.") gives .NULL., even EVAL("NULL") returns VFPs boolean value of .NULL.

Doing that with scan and field by field, you could also almost write a text reader/converter doing it while reading the file in. But obviously, nothing in VFP itself beats getting the CSV text into table rows and split at commas, if you have all char columns and thereby avoid any unwanted conversions, so you circumvent these bad automatisms minus the one about not respecting double quoted values with linefeeds in them.

I never needed it that way around, I'd perhaps even go through a MySQL database, let that read in CSV and query the data from there. Seems overly complicated, but it isn't once you have a development server up and running anyway. It's obviously a thing you avoid using in a product introducing a dependency to a SQL server installation just for that feature.

You may get inspiration from the opposite topic of exporting to CSV I covered in faq184-7770 with EVAL() playing the inverse role of TRANSFORM(), EVAL() results in whatever type of an expression or value in textual form, just as TRANSFORM() converts any type of value into its string representation. With some additional caution, as dates in the form 4/25/2019 become the numerical result of a division. A general routine can't rely on the strings coming from CSV to uniquely define their type, so a general CSV import routine can only rely on the type the destination field has and make decisions whether to use EVAL() or other functions like CTOD(). The only way EVAL() is a wholesome solution is, you specify data in a way also EVAL correctly converts it, eg using {^YYYY-MM-DD} format for dates. But tell an MSSQL admin or whatever source database admin/developer to export in that specific way for VFP.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

If this is about data exchnage, VFP is not very good in XML, but at least better than in CSV, so if you can get data as XML export, you'll likely be able to read that in with VFPs XML classes or West Windows XML libraries (not so much with XMLTOCURSOR, which mainly only converts back what CURSORTOXML generates but with XMLAdapter, XMLTable, etc.).

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[atlopes]

Wangta,

The CSVProcessor at VFPX (

https://github.com/atlopes/csv)

addresses that issue by setting a NullValue property. In your case

.NullValue = ""

which, by the way, is the default setting, will interpret empty fields in the CSV file as NULL.

So, something like this

* where the data will be stored
CREATE CURSOR SomeData (Column1 Double NULL, Column2 Double NULL, Column3 Double NULL)

* put some values just to demonstrate we are appending
INSERT INTO SomeData VALUES (PI(), RAND(-1), RAND())

* the CSV source file
LOCAL Source AS String
TEXT TO m.Source NOSHOW
9.0234,,10.3211
ENDTEXT

STRTOFILE(m.Source, "exported.csv") 

LOCAL CSV AS CSVProcessor

* instantiate the processor
m.CSV = CREATEOBJECT("CSVProcessor")

* we don't have any rows
m.CSV.HeaderRow = .F.
* and data will be appended
m.CSV.WorkArea = "SomeData"
* 0 means Ok
? m.CSV.Import("exported.csv")

* show the result
SELECT SomeData
GO TOP
BROWSE

will result in something like this