Importing CSV Files

DesB

I am having problems with CSV files.
Have taken excel tables, saved them as CSV files, and then tried to import these CSV files into GS.
What I get in GS is Data separated by commas (which sounds right...CSV stands for "comma separated values" after all !!
However, when I look at TSB's video tutorials, the imported CSV data in GS shows up as per normal excel spreadsheet data...ie without the commas!!!
Do you have any advice for me?....Is the problem with my excel preferences?
Many thanks

calvin9403

humm what do you mean by separate with commas?


CAlvin

DesB

Hi Calvin...All the data is crammed into one column, separated by Commas.....It is as if it does not see more than one column
I can send you a screenshot...please send an address
Many thanks for your interest
Des B

calvin9403

calvin@calvinhsu.co.cc

and sent me the file I can fix it for you

CAlvin

MotherHoose

open the CSV file in textEdit … does it look squirrelly there? … then problem is with excelFile

if it looks fine in textEdit … then you may have unsupported symbols in it … or cells with 2 different values for the data …
EX: $5.50 (that could be all text; or text and real 5.50) …
and may be your excelPreferences … but an export of the file should not include those.

look at the values GS allows for cells … keep/make your spreadsheet cellContent in only one value type/cell

I have done lots of GS imports of CSV from down-and-dirty written in textEdit … to large Numbers.app tables … without problems

@DesB … my address in Message … just sent to you … you could even send me the excelFile with the screenshot.

@};- MH

calvin9403

hey des sent you back the email

DesB

Hey Calvin and MH...Thanks so much for all the effort you are going through to help me!!
In short though, I have not had much success solving my problem.
Calvin....you have suggested that I open my CSV file using TextWrangler...I did so, and it opens the same way....with commas separating all the cells!!...and I did not receive the attachments that you sent me?
MH, I have created a small dummy file in excel, using only plain text...and saved it as "CSV".....but when I try and import it to GS, it shows all the cells in one column, separated by semi-colons....If I had your address, I would send you screenshots to look at !
By the way....I have opened my CSV files in TextEdit...and it also displays all the cells separated by semi-colons...
I really would appreciate you two guys giving me more help...Many thanks
DesB

calvin9403

sorry des I forgot to attach the file

open up the file in text wrangler and then remove the semi colons

Calvin

Chunkypixels

@DesB your not alone in your troubles with importing pre-formatted data through CSV files.

MotherHoose kindly explained in another thread that CSV format wasnt designed to incorporate many of the symbols such as $£"-' etc... some newer software can export CSV with them, but its a bit hit and mix across the board. So it could be something to do with that.

Im also coming to the conclusion though that there could be an unanounced, coded limit to the size of the Tables imported into GS. Not necessarily by how many row/column cells there are, but by how much total data is actually in the combined cells. My own tests are leading me to believe that they were never really tested with large file imports or sizeable Table data.

It would be helpful if GS could actually give us a detailed technical spec of the Tables, and the CSV import format theyve used.

MotherHoose

spreadsheets are very easy — spreadsheets are very quirky

for those spreadsheets you want to import into GS tables as displayable Text…

*select all the cells in your spreadsheet and then Format > Cell Format as text*

if you want GS to manipulate/operate on the numbers in a column in your game … when imported into it
in your spreedsheet File:

*select that column with those number values … and change the Cell Format to number*

… (this will automatically strip the strings)
… import as: Not Defined / Mixed

if an Import … doesn't display correctly
√ your Export Preferences in your spreadsheet.app
or open that .csv file in textEdit … and see what the problem is
correct things and save and try another import in a newTable.

GS tries valiantly to import the contents of a .csv …
if that file is not formatted in standard/basic csv … its content will not display correctly.

in default usage GS Tables are for entering data …
the fact that GS gives us import function is just heaven!
… it is up to us to make the content of our spreadsheets importable

@};- MH

MotherHoose

above post is thanx to … @DesB … for giving me an opportunity to look at your problem files …
and, finally figuring-out what I was doing naturally; but, what I wasn't making clear to others!

@};- MH

calvin9403

I have play around table a bit, try to make some weird symbols in the csv files such as ˆ¨©Á©Ï†Î®Œ®†∑Á´†¨®Á©¨˚ÓÔˆØÓ¨©Á√

it seems that gs can't handle most of them, but here is what you can try

open the csv file in bbedit(it works awesome) and then type the symbols you want

Calvin

MotherHoose

^ and & do import if the original cell format was Text.

other chars/symbols can be entered after import of text csvFile (define the data for import if you need to alter things in GS)
in GS click the menuBar Edit > Special Characters… if you do not know the [option]+ key for a char/symbol

columns of numbers you have imported as text can be changed to attribute type: integer; real; angle (and boolean 8-} )

again… the default usage of GS tables is for data entry
Importing .csv is a big,big + (even if you have to make minor changes to the data in GS table!)

@};- MH

BonZero

I tried to import some csv files today and kept getting weird tables like DesB mentioned before, after trying different options, I found that Google Docs, exports perfect CSV files, didn't have problems with special characters or anything, just perfect columns and GS displays the data as it appears on different programs, I was getting desperate but Google Docs worked fine for me

MotherHoose

ah, that is so nice to know @BonZero

thanx for the heads-up

@};- MH

BonZero

thanks MotherHoose, BTW after further examination I have discovered that the comma "," and semicolon ";" are the only symbols causing problems, if you enclose the sentence (the question of a quiz game with all sorts of characters for example) between quotation marks the problem is gone, the columns are separated by simple commas, and that's it, uff finally I could have the tables as I wanted, I think the CSV format must be identical to php code etc...

BonZero

mm finally found that the only characters giving problems are= "&" and "," and ,"quotation marks next to a comma, the rest are ok and there's no need to do anything else than separating columns with regular commas... finally got my tables sorted

dugistheking

Hello. I read somewhere that the limit is 999 for each row/column. I have around 6 tables I want to fill for a word game, each with around 5000 words. The data imports fine. However when you play not all the words are displayed. When you go into the table to look at the data, suddenly 2 columns are missing. Has anyone else had this issue? Does anyone know a fix? Does anyone know if GS are working on sorting this out for the next release and when? I cant be the only one having large amounts of data? Many thanks for the help as always GS community. An official answer from a GS team would be fantastic.