How to overcome 254 character limit importing CSV or excel files 3

[linousa]

How to overcome 254 character limit importing CSV or excel files?
I have a table, I need import CSV with a few columns that are about 1000 characters long, maybe creating 4 fields for each, but how to split them?

 

[Mike Lewis]

The 254-character limit applies to character fields within a table. It does not apply to character variables, and not to memo fields. In both those cases, the limit is 16 MB.

You cannot use IMPORT or APPEND to import memo fields from a text file, but you can use low-level file functions, that is commands such as FOPEN() and FREAD(), to get the text file into memory variables, from where it can be inserted into the table. Alternatively, if the entire text file is less thatn 16 MB, you could use FILETOSTR() to get the entire text into one variable, and then parse out the individual fields into memo fields.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[atlopes]

Linousa,

You may try the CSVProcessor class at

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

It's able to import columns larger than 254 characters either into a prepared table/cursor or by creating a new one.

The class is documented, examples included, and you may use it as it is, or adapt if needed.

 

[Chris Miller]

If you want to use your own idea of splitting, you can also use TYPE SDF, which won't split the text fiel at commas, but with given widths, so four C(250) fields would read in 1000 character lines. But itwould be even simpler to read in text file lines with FGETS as Mike suggests.

I think you better use what atlopes offers, because in the end I think your next step in your own idea is to glue the pieces together again into a memo.

Or you let excel do the major work of loading and splitting the csv data into a sheet and then finally do

#Define xlLastCell 11

Create Cursor Import(....fields as needed for the csv...)

WITH oExcel.ActiveWorkBook.ActiveSheet
  loLastCell = .Cells.SpecialCells( xlLastCell )

  laData = .Range( .Cells(2,1), m.loLastCell ).Value && NOTE: asusmes data starts in row 2, row 1 just has the captions.
ENDWITH

Insert into Import From Array laData

Chriss

 

[linousa]

Atlopes,

How do you override field types in CSVProcessor? It randomly creates memo and varchar fields for values with >254 characters. Also, how do you format "overflow" field names(when it creates extra fields for values with >254),it names them ?

Also, when I add it to my project, getting this:

Added csv,csv-processor and namer in programs. Calling it from button click event on the form:

DO "csv.prg"

LOCAL CSV AS CSVProcessor
m.CSV = CREATEOBJECT("CSVProcessor")


IF m.CSV.Import(GETFILE("csv"), "tempCSV") = 0
SELECT tempCSV
BROWSE
ELSE
* an error occurred
ENDIF

Thank you!

 

[atlopes]

Linousa,

Can you provide the first line of your CSV file, the one with the header row? When a cursor is created, the name of the columns is derived from that row, but you can also import the data into an existing cursor.

In order to integrate the class into a project, you must also include the VFP name processor class (that is, vfp-names.prg). See if this solves your issue.

 

[linousa]

1. Header:
Greeting,NameLine,straddress,strAddress2,strCity,strState,strZip,strRegion,strPostalCode,strCountry,strCustomerID,ExpDate,YearMember,
VariableText1,VariableText2,VariableText3,VariableText4,AdditionalText,strCompanyName,MemberType,Num

2. Is it possible to split >254 character columns into multiple during import?

3. How do you import the data into an existing table?

4. vfp-names.prg - worked, thank you!

 

[atlopes]

Linousa

I duplicated and saved your header row into a file and imported it into a cursor. This is the resulting structure:

Can you double-check on your CSV source? I also noticed that 1.dbf seems to be a free table, hence the 10 character limit for column names. That is not the cursor that the CSVProcessor created, is it?

To append into an existing cursor or table, set the WorkArea property to the name or number of the cursor or table. You can see an example (including field mapping and filtering) at

https://github.com/atlopes/csv/blob/master/examples/fieldmapping.prg

Hope this helps.

 

[linousa]

Not sure how to call table designer on cursor, so I save to free table(copy to). This is what I get now:

It is possible to split to varchar/character instead of memo?

 

[linousa]

Came up with the temporary solution, assuming max 508 character limit for each of those 4 memo fields:

Select *,;
	LEFT(variabletext1,254) As v11, ;
	LEFT(Substr(variabletext1,255,254),254) As v12, ;
	LEFT(variabletext2,254) As v21, ;
	LEFT(Substr(variabletext2,255,254),254) As v22, ;
	LEFT(variabletext3,254) As v31, ;
	LEFT(Substr(variabletext3,255,254),254) As v32, ;
	LEFT(variabletext4,254) As v41, ;
	LEFT(Substr(variabletext4,255,254),254) As v42 ;
FROM tempcsv ;
INTO Table mytest

 

[atlopes]

Linousa,

Just a final note: can you give one or two examples of the contents of the "expdate" column? I assume you would like to have it as a date, not as a string...

 

[linousa]

How do I change field types? Is it possible to have them hard coded, all be varchar's?

 

[atlopes]

Linousa,

If you want full control of column types, create the cursor previously and append the CSV data to it.

If you want all columns to be varchar or memo (for lengths greater than 254), other than append into a cursor you may subclass the CSVProcessor class:

LOCAL CSVP AS CSVP_AllVarchar

m.CSVP = CREATEOBJECT("CSVP_AllVarchar")

? m.CSVP.Import(GETFILE(), "tmpCSV")

DEFINE CLASS CSVP_AllVarchar AS CSVProcessor

	FUNCTION ScanNumber (Source AS String) AS Number
		RETURN .NULL.
	ENDFUNC

	FUNCTION ScanLogical (Source AS String) AS Boolean
		RETURN .NULL.
	ENDFUNC

	FUNCTION ScanDate (Source AS String, IsTime AS Boolean, UseRegularExpression AS Logical) AS DateOrDatetime
		RETURN .NULL.
	ENDFUNC

	FUNCTION ScanBinary (Source AS String) AS Blob
		RETURN .NULL.
	ENDFUNC

ENDDEFINE

If you want to have the "expdate" column being interpreted as a date, set the DatePattern property to "$2D/%2M/%4Y".

 

[Chris Miller]

Not sure how to call table designer on cursor, so I save to free table(copy to).

There's the step you lose tha long field names. CREATE DATABASE TEST, then COPY TO some.dbf DATABASE TEST and the long field names are kept.
Or simply use AFIELDS() to get fields and their types.

Or simply BROWSE the cursor and you see the names as they should be.

Chriss

 

[linousa]

Added csvp.prg with:

DEFINE CLASS CSVP_AllVarchar AS CSVProcessor

	FUNCTION ScanNumber (Source AS String) AS Number
		RETURN .NULL.
	ENDFUNC

	FUNCTION ScanLogical (Source AS String) AS Boolean
		RETURN .NULL.
	ENDFUNC

	FUNCTION ScanDate (Source AS String, IsTime AS Boolean, UseRegularExpression AS Logical) AS DateOrDatetime
		RETURN .NULL.
	ENDFUNC

	FUNCTION ScanBinary (Source AS String) AS Blob
		RETURN .NULL.
	ENDFUNC

ENDDEFINE

Calling it:

Do 'csvp.prg'
LOCAL CSVP AS CSVP_AllVarchar
m.CSVP = CREATEOBJECT("CSVP_AllVarchar")

If m.CSVP.Import(GETFILE(), "tmpCSV") = 0
	Select tempCSV
*  BROWSE
Else
* an error occurred
ENDIF

Getting:

 

[atlopes]

Linousa,

You must SET PROCEDURE TO (LOCFILE("csvp.prg")) ADDITIVE, or include the class definition in the program that instantiates the object (as I did in the snippet that I posted).

 

[linousa]

Sorry guys, I never used OOP in vfp before!

atlopes,
When I am call it from button.click event on the form with your snippet:

myearwood,

 

[atlopes]

Linousa,

You cannot have a class definition inside a method. Hence the error.

Put your subclass within scope by issuing SET PROCEDURE TO "csvp.prg" ADDITIVE, or use the method that Mike suggested (just change the OF clause to OF csv.prg, or simply omit it - the CSVProcessor class is probably already within scope by then).

 

[linousa]

It worked, thank you guys!

P.S.
atlopes,
Will it be hard to add field overflow feature to CSVProcessor?

 

[atlopes]

Will it be hard to add field overflow feature to CSVProcessor?

Importing the CSV data into a VFP cursor opens the door to more specific post-processing steps, like the one you did to achieve the results you were looking for. Generating more columns than the source data has is not something I would set as a foreseeable goal.