Invisible return lines in CSV files?
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller
Invisible return lines in CSV files?
Hey all,
I'm trying to import a CSV File into a data grid. My issue is there seems to be hidden crs or returns in the data that livecode cannot replace.
Attached the stack and CSV
What seems to happen is that the new line gets added into the data grid and creates a new line, but I'd rather have the data stay in a single cell rather than start a new line with each return.
Thoughts?
My biggest would be that I'm decoding into UTF-8 which I need for foreign languages. But I did try the code 0x0D which is accepted, but doesn't change my situation. I've tried several different codes and hexes but they don't seem to catch the ones in the attached CSV.
Edit:
You can cut out the UTF-8 Decode line, one because it has the wrong variables, and two it doesn't change anything.
~~~~~~~~~~~~~~~~~~~~~~~~Code~~~~~~~~~~~~~~~~~~~~~~~~
on mouseUp --import text csv file
--Local variables used below.
local fPath, dgInput, dataline, dataItem, dgOutput
--The intial import of the file
answer file "Choose a text .csv spreadsheet file:" with (specialFolderPath("desktop") ) with type "Text CSV|csv|" --specify folder, restrict file type
if it = empty then exit to top
put it into fPath
put url ("file:" &fPath) into dgInput --import text file into temp container
--Decoding the file to UTf-8 which accepts the most foreign languages Which I work with.
put textDecode(tspread,"UTF-8") into tSpread
--Since default was comma from my understanding.
set the itemDel to tab
--Clear out the field.
put empty into dgOutput
repeat for each line dataLine in dgInput
repeat for each item dataItem in dataLine
put replaceText(dataItem,cr,"Return Found") after tOutput
--have tried, return and 0x0D
end repeat
put cr after tOutput
--because I do want newlines at the end of each line
end repeat
--Putting the information of the import into the fields.
put tOutput into fld 1
end mouseUp
I'm trying to import a CSV File into a data grid. My issue is there seems to be hidden crs or returns in the data that livecode cannot replace.
Attached the stack and CSV
What seems to happen is that the new line gets added into the data grid and creates a new line, but I'd rather have the data stay in a single cell rather than start a new line with each return.
Thoughts?
My biggest would be that I'm decoding into UTF-8 which I need for foreign languages. But I did try the code 0x0D which is accepted, but doesn't change my situation. I've tried several different codes and hexes but they don't seem to catch the ones in the attached CSV.
Edit:
You can cut out the UTF-8 Decode line, one because it has the wrong variables, and two it doesn't change anything.
~~~~~~~~~~~~~~~~~~~~~~~~Code~~~~~~~~~~~~~~~~~~~~~~~~
on mouseUp --import text csv file
--Local variables used below.
local fPath, dgInput, dataline, dataItem, dgOutput
--The intial import of the file
answer file "Choose a text .csv spreadsheet file:" with (specialFolderPath("desktop") ) with type "Text CSV|csv|" --specify folder, restrict file type
if it = empty then exit to top
put it into fPath
put url ("file:" &fPath) into dgInput --import text file into temp container
--Decoding the file to UTf-8 which accepts the most foreign languages Which I work with.
put textDecode(tspread,"UTF-8") into tSpread
--Since default was comma from my understanding.
set the itemDel to tab
--Clear out the field.
put empty into dgOutput
repeat for each line dataLine in dgInput
repeat for each item dataItem in dataLine
put replaceText(dataItem,cr,"Return Found") after tOutput
--have tried, return and 0x0D
end repeat
put cr after tOutput
--because I do want newlines at the end of each line
end repeat
--Putting the information of the import into the fields.
put tOutput into fld 1
end mouseUp
- Attachments
-
- CRTest.zip
- (1.43 KiB) Downloaded 131 times
-
- VIP Livecode Opensource Backer
- Posts: 9580
- Joined: Wed May 06, 2009 2:28 pm
- Location: New York, NY
Re: Invisible return lines in CSV files?
Hi.
Welcome to the forum and LC.
There are many threads about the evils of CSV.
Your handler did not run, because the line "continue" is not a valid command, nor is there a handler it might call. There are syntax errors, but that is normal, and you will get better as you learn. Lots of things to talk about once we get through the immediate problem.
One thing I noticed is that you never load a dataGrid with anything, nor do I see a DG at all. Your stack does not tell me what you are trying to do, or where you want to go. This is normal as well. It just takes a little back and forth.
So, back to you
Craig
Welcome to the forum and LC.
There are many threads about the evils of CSV.
Your handler did not run, because the line "continue" is not a valid command, nor is there a handler it might call. There are syntax errors, but that is normal, and you will get better as you learn. Lots of things to talk about once we get through the immediate problem.
One thing I noticed is that you never load a dataGrid with anything, nor do I see a DG at all. Your stack does not tell me what you are trying to do, or where you want to go. This is normal as well. It just takes a little back and forth.
So, back to you
Craig
Re: Invisible return lines in CSV files?
Hello dunbarx,
Thank you for jumping in to help me out.
I'm not sure where the "continue" line is being held.
You are correct, I am not loading the datagrid within the code, I assumed that with the ending line
put tOutput into fld 1
I'll poke around more tutorials on datagrid (If you have some direct ones let me know and I'll try to save your time). The above had just given the illusion of working. XD
Thank you for jumping in to help me out.
I'm not sure where the "continue" line is being held.
You are correct, I am not loading the datagrid within the code, I assumed that with the ending line
put tOutput into fld 1
I'll poke around more tutorials on datagrid (If you have some direct ones let me know and I'll try to save your time). The above had just given the illusion of working. XD
-
- VIP Livecode Opensource Backer
- Posts: 9580
- Joined: Wed May 06, 2009 2:28 pm
- Location: New York, NY
Re: Invisible return lines in CSV files?
Since you were initially concerned about invisible returns, run something like this in a test handler with your imported data:
What do you get?
As for dataGrids, they are compact and powerful, and require a learning curve all on their own. Do you need to use them right at the beginning? There may be other gadgets that will do as well.
Did I mention you had syntax errors?
Craig
Code: Select all
...
repeat for each char tChar in yourImportedData
if tChar = return then add 1 to returnCount
end repeat
answer returnCount
...
As for dataGrids, they are compact and powerful, and require a learning curve all on their own. Do you need to use them right at the beginning? There may be other gadgets that will do as well.
Did I mention you had syntax errors?
Craig
Re: Invisible return lines in CSV files?
Hi,
your CSV is encoded as "Win-1252" & uses LF (Unix) as well as CRLF (Win) line endings. (If working with CSV a capable text editor is worth a lot ... )
And you read the file using "URL ("file:" & ...)", which causes LC to mess up all the returns :)
Read the file as "URL ("binfile:" & ...)" instead, and then you'll see the LF as 10, and the CRLF as 13,10 (using "CodePointToNum()").
I assume the LF are meant as CR within a record? Getting this resolved can be tricky. A quick and dirty way:
Result is:
with CR line endings and "ø" where there's a CR within a record.
Now you can use this to play further ;-) Have fun!
your CSV is encoded as "Win-1252" & uses LF (Unix) as well as CRLF (Win) line endings. (If working with CSV a capable text editor is worth a lot ... )
And you read the file using "URL ("file:" & ...)", which causes LC to mess up all the returns :)
Read the file as "URL ("binfile:" & ...)" instead, and then you'll see the LF as 10, and the CRLF as 13,10 (using "CodePointToNum()").
I assume the LF are meant as CR within a record? Getting this resolved can be tricky. A quick and dirty way:
Code: Select all
answer file "Which file?"
if it is empty then exit repeat
put URL ("binfile:" & it) into myFile -- get data
repeat for each char C in myFile -- change text to list of nums
put CodePointToNum(C) & comma after myVar
end repeat
replace ",13,10," with ",30," in myVar -- replace CRLF with placeholder
replace ",10," with ",248," in myVar --> replace LF with ø
replace ",30," with ",10," in myVar -- change placeholder to CR
delete char -1 of myVar
repeat for each item I in myVar -- and restore the text
put NumToCodePoint(I) after myResult
end repeat
set the clipboardData["text"] to myResult -- ready for output
--> ø is now where LF was
Code: Select all
"This isøA newline Test"^
this is a normal line^
"This is anotherøSplit Line"^This is a normal line on the split
This is a normal next to a split^"This is aøSplit line"
Now you can use this to play further ;-) Have fun!
All code published by me here was created with Community Editions of LC (thus is GPLv3).
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
If you use it in closed source projects, or for the Apple AppStore, or with XCode
you'll violate some license terms - read your relevant EULAs & Licenses!
Re: Invisible return lines in CSV files?
Here an interesting read with a nifty script, which may be helpful:
https://www.fourthworld.com/embassy/art ... t-die.html
https://www.fourthworld.com/embassy/art ... t-die.html
Re: Invisible return lines in CSV files?
Thanks all for jumping in to help.
@dunbarx I had tried various ways for testing the returns and getting nil. With AxWald's observation and input I was able to progress.
For future land, is there a way to enforce syntax in similar ways to visual studio within live code? As you probably guessed I'm still new, so the best answer I've seen from searching "Enforce Syntax LiveCode" seems to amount to putting bools into my script, and or getting good. While I plan on the latter, the first seems a bit odd.
I'll also be looking into table fields as that's what I accidentally added in the example. Because you're right DG are big scary tools, but I like all the pretty options.
@AxWald awesome find! Thank you for getting things moving again on my end. I'm experimenting around with the sample you gave me. The next road block is to figure out foreign languages. (Thai seems to be getting stuck on ลิ) despite being able to code up most of the other characters. I'll look into exception handling or something here.
@Klaus.... You're the hero I deserve, but not the one I need. Being in the spot that I am, I'll look through the forums for alternatives. TLDR I need to get Excel sheets into LiveCode and I'm not too keen on throwing down the money on extensions when I know so little LiveCode. CSV was the quick middle ground. To keep this topic concise, let me know if there's a forum thread I can read. Or I'll just start a new one soon.
Again thanks all for the support!
@dunbarx I had tried various ways for testing the returns and getting nil. With AxWald's observation and input I was able to progress.
For future land, is there a way to enforce syntax in similar ways to visual studio within live code? As you probably guessed I'm still new, so the best answer I've seen from searching "Enforce Syntax LiveCode" seems to amount to putting bools into my script, and or getting good. While I plan on the latter, the first seems a bit odd.
I'll also be looking into table fields as that's what I accidentally added in the example. Because you're right DG are big scary tools, but I like all the pretty options.
@AxWald awesome find! Thank you for getting things moving again on my end. I'm experimenting around with the sample you gave me. The next road block is to figure out foreign languages. (Thai seems to be getting stuck on ลิ) despite being able to code up most of the other characters. I'll look into exception handling or something here.
@Klaus.... You're the hero I deserve, but not the one I need. Being in the spot that I am, I'll look through the forums for alternatives. TLDR I need to get Excel sheets into LiveCode and I'm not too keen on throwing down the money on extensions when I know so little LiveCode. CSV was the quick middle ground. To keep this topic concise, let me know if there's a forum thread I can read. Or I'll just start a new one soon.
Again thanks all for the support!
-
- VIP Livecode Opensource Backer
- Posts: 9802
- Joined: Sat Apr 08, 2006 7:05 am
- Location: Los Angeles
- Contact:
Re: Invisible return lines in CSV files?
Did you read the link Klaus provided?moogen wrote: ↑Thu Apr 01, 2021 2:31 am@Klaus.... You're the hero I deserve, but not the one I need. Being in the spot that I am, I'll look through the forums for alternatives. TLDR I need to get Excel sheets into LiveCode and I'm not too keen on throwing down the money on extensions when I know so little LiveCode. CSV was the quick middle ground. To keep this topic concise, let me know if there's a forum thread I can read. Or I'll just start a new one soon.
Where on that page are you asked for money?
Richard Gaskin
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn
LiveCode development, training, and consulting services: Fourth World Systems
LiveCode Group on Facebook
LiveCode Group on LinkedIn
Re: Invisible return lines in CSV files?
Not on that page. Different LiveCode Excel Library extensions.FourthWorld wrote: ↑Thu Apr 01, 2021 2:55 amDid you read the link Klaus provided?
Where on that page are you asked for money?
Re: Invisible return lines in CSV files?
Then it was VERY BAD wording in a wrong context!moogen wrote: ↑Thu Apr 01, 2021 3:15 amNot on that page. Different LiveCode Excel Library extensions.FourthWorld wrote: ↑Thu Apr 01, 2021 2:55 amDid you read the link Klaus provided?
Where on that page are you asked for money?
Re: Invisible return lines in CSV files?
Sorry Klaus but not sure I agree - wording was fine.Klaus wrote: ↑Thu Apr 01, 2021 9:10 amThen it was VERY BAD wording in a wrong context!moogen wrote: ↑Thu Apr 01, 2021 3:15 amNot on that page. Different LiveCode Excel Library extensions.FourthWorld wrote: ↑Thu Apr 01, 2021 2:55 amDid you read the link Klaus provided?
Where on that page are you asked for money?
The OP states:
It's clear he's talking about paying for extensions - the Excel extensions - and not a script to import CSV - and yes I'd recommend the link to Richard's page and yes it's free...but not sure there was any confusion about this!
Re: Invisible return lines in CSV files?
Yes, you are right, sometimes it is not easy to express something if you are a non-native english speaking person.
Sorry, moogen, peace!
So it was only an unhappy context.
Sorry, moogen, peace!
So it was only an unhappy context.