[Solved] Set CSV separator and text delimiter when saving

[Geoff Moore]

I'm trying to use a Game Maker text engine which reads dialogue from this csv file: https://www.dropbox.com/s/bnflfd3t6uloc ... s.csv?dl=0

When I import it into Open Office and select 'Comma' under Separator Options everything is fairly readable, with other options it's jumbled together. The problem is that commas in dialogue also create a break, which messes up the columns. I need to avoid comma separation.

In the document itself this advice is given: "The .csv files themselves may or not require a different delimiter depending on what text you're using. The default method of delimiting cells is a comma. Naturally, this gets in the way of generic speech which does tend to use rather a lot of commas. I recommend using a vertical bar (ASCII character 124) to delimit cells and a grave accent (character 96) to delimit text. These options are readily adjustable when saving your .csv file (you may need to use Save As in some programs."

I don't know how to set ASCII characters to delimit cells, this is all way over my head. Can someone talk me through how I should be opening/saving the spreadsheet so it is readable and usable? I linked the file up at the top.

Many thanks for reading!

[RusselB]

In the Text Import Dialog, there is an option marked Other for the separator. In this location you can enter the character(s) that you want to use as the separator... Multiple characters can be entered.
How you get the correct character into the file initially will depend on the options available from the program that is generating the file.

[Geoff Moore]

Thanks for replying! So how would I enter an ASCII character in that box?

The file isn't generated by the program, the idea is I make the CSV and the program reads from it. The file I linked is the example that came with the project, so I need to open that in Calc so I can fiddle around with it and see how it works, and I just need to know what calc settings to use to get it to display correctly (i.e. without commas in the dialogue separating the columns). It would be easier to see what I mean if you have time to download the file and open it in calc.

[RusselB]

I did download and open the file, and I can see the difficulties you mention.
Slight misunderstanding.. you want to generate the file using Calc so that the other program can read it, not (as I originally interpreted your post) the other way around.

Any character that is on your keyboard is an ASCII character. I would strongly recommend using a character that is on your keyboard, rather than one that has to be generated using an ASCII character code.
If you insist on using a character that needs to be generated using a character code, then these can be entered using the Alt key and a number sequence entered using the numeric keypad. NOTE: The numbers across the top of your keyboard will NOT work for entering the character code.

[coray80]

Is the attached what you're wanting?
char124 is the shift-backslash on the key board
char96 is the character below the tilde (above the tab key)

[Geoff Moore]

@RusselB: I don't mind what characters are used to delimit, as long as it's not something I'm likely to use in dialogue - the reason I mentioned ASCII was because of the quote I posted in the OP from the linked document, I think they're suggesting those characters as they're very unlikely to be used in natural dialogue. I tried entering the ASCII codes and it didn't make any difference anyway - if I select anything other than comma it's all jumbled together. When you opened the document, were you able to set it so you could view unbroken stretches of text?

@coray80: When I open that file it still has column breaks where the commas fall in the dialogue, so it seems it's still separated by commas.

For example, B13 and C13's contents should just be in B13, but because of the comma in the sentence it's pushing it onto the next column. I need everything neatly in columns, but not separated by commas, if that makes sense.

[MrProgrammer]

Hi, and welcome to the forum.

In the document itself this advice is given: "… I recommend using a vertical bar (ASCII character 124) to delimit cells and a grave accent (character 96) to delimit text. These options are readily adjustable when saving your .csv file (you may need to use Save As in some programs."
I don't know how to set ASCII characters to delimit cells, this is all way over my head. Can someone talk me through how I should be opening/saving the spreadsheet so it is readable and usable?

That advice means: use File → Save As → File type → Text CSV → ✓ Automatic file name extension → ✓ Edit filter settings → Save → Character set → Unicode → Field delimiter → | → Text delimiter → `→ ✓ Save cell content as shown → OK. For the field/text delimiters, do not use the dropdown arrows to select standard characters; just type the | and ` in the dialog boxes.

Note the recommended text delimiter is ` and not '.  After you've saved the CSV file with these delimiters, you'll need to specify them again when you import it into Calc or into another program like "Game Maker text engine".

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Geoff Moore]

Thanks everyone! It turned out I needed to set " as the text delimiter when importing, everything displays correctly that way.