Please help - line feed and carriage return to separate column

[timmappa]

I have received CSV file with one column & each row have one records with several fields in it. Each row have name, Address & comments and each filed broken into line feed and carriage return ("OD" and "OA"). I am using Visual FoxPro to open this file in Excel and copy each filed into separate column. Do the same thing for entire file.

Example one column:

Row-1
Mr A B sample
Dear Mr Sample
Sample Street
Sample Road
Credit balance

Row-2
Mr x y Bond
Dear Mr Bond
Sample Street
Sample Road
Sample Suburb
No Balance
No Pay

I need to copy each field into separate column, so that I can import that into VFP table.

Some reason my logic is not working. Please help, if any one has any idea how to do this.

Thanks

 

[bborissov]

You didn't get CSV file, bewcause CSV means COMMA-separated value. You get some file that has CRLF as separator. How you know where some row ends and next row begins?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[JRB-Bldr]

Lets assume, like Borislav suggests above, that you are not truely getting a CSV file, but instead a TXT file showing data like you have above.

There are a variety of ways to approach this.

One approach...
Use the FILETOSTR() to read the entire file into a single string variable (example mcFileString).

Method 1:
Parse the 'field' data out of the string variable knowing how many text 'rows' make up a table record and populate your table with the extracted information.

Method 2:
Depending on the file's size you could
* --- Remove ALL CrLf's From the String & Repace With Comma ---
mcFileString = STRTRAN(mcFileString,CHR(13)+CHR(10),",") && Or Quote/Commas

* --- Now Insert CrLf Record Separators Only Where Desired ---
mcFileString = STRTRAN(mcFileString,"Mr ",CHR(13)+CHR(10)+"Mr ")
mcFileString = STRTRAN(mcFileString,"Ms ",CHR(13)+CHR(10)+"Ms ")
mcFileString = STRTRAN(mcFileString,"Mrs ",CHR(13)+CHR(10)+"Mrs ")
Use STRTOFILE() to write this new data back into a new CSV file.
The use the APPEND FROM <NewCSVFile> DELIMITED command to read data into table from the new CSV file.

Good Luck,
JRB-Bldr

 

[baltman]

The issue I see is that the data points are variable in each record. Assuming Balance is an anchor...

STORE CHR(13)+CHR(10) TO m.CRLF

&&make test file
STRTOFILE("","in.csv",0)
STRTOFILE("Row-1"+CRLF,"in.csv",1)
STRTOFILE("Mr A B sample"+CRLF,"in.csv",1)
STRTOFILE("Dear Mr Sample"+CRLF,"in.csv",1)
STRTOFILE("Sample Street"+CRLF,"in.csv",1)
STRTOFILE("Sample Road"+CRLF,"in.csv",1)
STRTOFILE("-200.00"+CRLF,"in.csv",1)
STRTOFILE(""+CRLF,"in.csv",1)
STRTOFILE("Row-2"+CRLF,"in.csv",1)
STRTOFILE("Mr x y Bond"+CRLF,"in.csv",1)
STRTOFILE("Dear Mr Bond"+CRLF,"in.csv",1)
STRTOFILE("Sample Street"+CRLF,"in.csv",1)
STRTOFILE("Sample Road"+CRLF,"in.csv",1)
STRTOFILE("Sample Suburb"+CRLF,"in.csv",1)
STRTOFILE("0.00"+CRLF,"in.csv",1)
STRTOFILE("No Pay"+CRLF,"in.csv",1)

&&build destination table
CREATE TABLE tblData (Rec_ID c(10), AcctName c(20), Msg C(20), ;
	Add1 c(20), Add2 c(20), Add3 c(20), Add4 c(20), Bal C(20),;
	PayStatus c(10))
	
&&Read data
m.cData = FILETOSTR("in.csv")

&&convert double CRLF to another character to aide in parsing rows
m.cData= STRTRAN(m.cData, m.CRLF + m.CRLF, CHR(1))

&&for each row originally determined by double CRLF
FOR m.i = 1 TO GETWORDCOUNT(m.cData, CHR(1))
	m.cString = GETWORDNUM(m.cData, m.i, CHR(1))
	APPEND BLANK 
	
	m.nOffset = 0
	&&for each data point determind by single CRLF
	FOR m.j = 1 TO GETWORDCOUNT(m.cString, m.CRLF)
		m.cDataPoint = GETWORDNUM(m.cString, m.j, m.CRLF)
		
		&&balance (only numeric) detected so offset j to 8 since Bal is field 8
		IF !EMPTY(m.cDataPoint) AND UPPER(m.cDataPoint)=LOWER(m.cDataPoint) 
			m.nOffset = 8 - m.j
		ENDIF 
		
		REPLACE (FIELD(m.j+m.nOffset)) WITH m.cDataPoint
	ENDFOR 
ENDFOR

ALTER TABLE DBF() ALTER COLUMN Bal n(20,2)

LOCATE
BROWSE NOWAIT

 

[NasibKalsi]

How you know where some row ends and next row begins?

This is the key information.

I assume you have blank line after each record.

Then the following may help you:

create table name t_main (F1 c(20), F2 c(20), ;
F3 c(20), F4 c(20), F5 c(20), F6 c(20),;
F8 c(20), F9 C(20)), FN1 n(10), FN2 n(10))

create table name t_raw (F1 c(40))
use
use t_raw alias t_raw in 0
select t_raw
appe from in.csv sdf

use t_main alias t_main in 0

select t_main

sele t_raw
go top

* First Line May be Blank, so skip it
do while len(trim(F1)) = 0 .and. .not. eof()
skip
enddo

do while .not. eof()
select t_main
append blank
i = 1 && Maximum number of Fields is 9
ic = ltrim(str(i,3))
select t_raw
do while len(trim(F1)) <> 0 ;
.and. i < 10 ;
.and. .not. eof()

replace t_main.F&ic with t_raw.F1
i = i + 1
ic = ltrim(str(i,3))
skip
enddo

* Skip All Blank Lines
do while len(trim(F1)) = 0 .and. .not. eof()
skip
enddo
enddo


* This code is not tested but will give you some idea. You could qualify the field in the while loop to know where they belong by using an array and case statement, and then replacing the t_main from the Array. Assuming Location 1 is always "Mr.", Location 2 is always "DEAR", and so forth. Then If left(F1,2) = "MR" then t_index = 1, ...

Hope it may help.


Nasib Kalsi

 

[ljupce]

Try this:
SELECT yourfile
lcTemp = Sys(2015)+'.tmp'
Copy To (m.lcTemp) fields field1,...,fieldx Type delimited With "" With Tab
lcString=Filetostr(m.lcTemp)
_Cliptext =lcString
Erase (m.lcTemp)
.Activeworkbook.Activesheet.Range('YOURRange').PasteSpecial()
YOURRANGE- where to start paste
** very fast way

 

[timmappa]

Thanks for reply,
I hope I have not confused you all explaining this problem properly. I can’t send a file to understand situation better. I will explain again:

This is comma (.CSV) separated file. I can use excel to open this file. Imagine data have two columns (column-a & column-b), per example in normal situation A1- Mr A B Sample & B1 - $30.50 and field A1 & B1 separated with comma.

But file I have received situation is not like above, it is as follows:
A1 have

Mr A B sample
Dear Mr Sample
Sample Street
Sample Road
Sample Suburb
Sample Country

A2 have

Mr X Y Xample
Dear Mr Xample
Xample Street
Xample Road
Xample Suburb
Xample Country


B1 have

116.82
1217.37
10.67
266.67
270.27
44.44
47.11
248.89
177.78
195.56

B2

916.32
9217.47
90.57
966.77
970.87
94.14
97.21
948.99
977.68
995.26

So on

Each lines finish with line feed and carriage return except last line, example in A1, end of < Mr A B sample>, end of <Dear Mr Sample> and so on have a line feed and carriage return and last line <Sample Country> NO line feed and carriage return

Same in B1, end of <116.82> line feed and carriage return, end of <1217.37> & so on

What I am trying to do read each row & copy each field in to separate column.
Example A1 have 6 fields, copy these fields in to separate column like as follows:

C1- Mr A B sample
D1- Dear Mr Sample
E1- Sample Street
F-1 - Sample Road
G-1 - Sample Suburb
H1- Sample Country

Do same thing with A2
Same thing with B1, copy individual value in to L1, M1, N1, O1, P1, Q1, R1, S1, T1, So on

baltman (TechnicalUser) – in your file, field are in separate row

A1- Row-1
A2- Mr A B sample
A3- Dear Mr Sample
A4- Sample Street
A5- Sample Road
a6- -200

But in my scenario, entire thing in A1 & end of <Row-1> there is a line feed and carriage return and end of <Mr A B sample>, & so on

bborissov (Programmer) & jrbbldr (Programmer)

Definitely this is comma separated file; I hope I have explained the file situation better. I can open this file in Excel; I can see number of lines in each row when I keep the cursor. I need to read each filed until end of the row copy each filed into individual column & move to next row….

NasibKalsi (Programmer) – I can’t import this file to VFP table using SDF format.
This will not work.

 

[NasibKalsi]

I think, the only problem with my solution is that when you import into a single field name

the last Field of the NAME String will be with the First field of the Numeric Number.

Example:

Your table will look like this:

record 1 Mr A B sample
record 2 Dear Mr Sample
record 3 Sample Street
record 4 Sample Road
record 5 Sample Suburb
record 6 Sample Country, 116.82
record 7 Mr X Y Xample

and so on.


If this is true, then with some modification to my logic you will be fine. Try opening the file in wordpad, how does it display ?

Nasib Kalsi

 

[JRB-Bldr]

OK, the file that you get has a CSV file extension.

But when you look at the data it is not formated as a CSV. If it were then the data for each of your displayed rows would not be separated by CHR(13)CHR(10). Instead it would be separated by a Comma (hence the name Comma Separated Variable).
In a TRUE CSV new 'rows' are separated by CHR(13)CHR(10), not individual 'field' values. Consequently the file is not a TRUE CSV file despite the file extension it arrives with.

Regardless, a question about how you say the data comes to you....

Each lines finish with line feed and carriage return except last line, example in A1, end of < Mr A B sample>, end of <Dear Mr Sample>

How does the last line end?

Have you used a Hex Editor (or some other tool) to review the data in its 'raw' form to determine what does terminate the last row entry?

From the appearance of the data as shown above we might assume that it is CHR(13)CHR(10)CHR(13)CHR(10) (2 carriage Returns/Line Feeds).

If so then you could use a modification of my suggestion above to process the file

* --- Read File Text Into A String Variable ---
mcFileString = FILETOSTR(MyFile)

* --- Remove ALL 'End of Record' Markers From the String & Repace With Some Unique Indicator ---
mcFileString = STRTRAN(mcFileString,CHR(13)+CHR(10)+CHR(13)+CHR(10),"*R*")  

* --- Now Replace ALL Other Remaining CrLF's With a Comma ---
mcFileString = STRTRAN(mcFileString,CHR(13)+CHR(10),",")  

* --- Since You Know The Unique 'Record' Separator, Now Change It Back ---
mcFileString = STRTRAN(mcFileString,"*R*",CHR(13)+CHR(10))

* --- At This Point The Data Is In TRUE CSV Format ---
Use STRTOFILE() to write this new data back into a new CSV file.
Then use the APPEND FROM <NewCSVFile> DELIMITED command to read data into table from the new CSV file.

Or you could use a more intensive Text Parsing routine to process the data.

Good Luck,
JRB-Bldr

 

[timmappa]

Thanks for all your helps - it works