*.csv formats and MS DOS CSV formats

users
#1

Hi list

My wife needs to submit *.csv files to the UK HMRC dept however, they request that these are sent as *.csv ms-dos format.

Having saved a calc sheet as a *.csv file, she tested this by opening it up with MS Excel, and found several errors that made the file unreadable.

How can I advise her to save spreadsheets as *.csv formats in a way that HMRC can read these as if saved in the MS Dos CSV format (which LibO doesn't have the option to do)? Is there something in the save procedure that she needs to attend to?

Thanks for any help, because it will be important to get this right as I have just (successfully) advocated that the charity she works for adopts LibO in favour of MS Office. If the HMRC cannot read CSV files saved in LibO, then this will be a deal breaker for the charity as they have to submit such files regularly.

Cheers

#2

Hi,

> How can I advise her to save spreadsheets as *.csv formats in a way that

HMRC can read these as if saved in the MS Dos CSV format (which LibO
doesn't have the option to do)? Is there something in the save procedure
that she needs to attend to?

Sorry if I appear a bit dim, but CSV literally stands for Comma Separated Values, with a line feed/carriage return symbol at the end of each line. Unix and MS-DOS line feeds/newline commands are different, e.g. Unix relies on \n to indicate a newline, whereas MS-DOS (and Windows) relies on \n\r (or LF/CR). Is this the type of formatting you are looking for ?

What exactly are the errors that your wife sees when opening the CSV file in Excel ?

Alex

#3

Hi :slight_smile:
When saving as Csv you get various options.

Probably the most crucial one is the "Field delimiter" drop-down which needs
to be set to

#4

Looking at this :

http://customs.hmrc.gov.uk/channelsPortalWebApp/channelsPortalWebApp.portal?_nfpb=true&_pageLabel=pageImport_ShowContent&id=HMCE_PROD_010685&propertyType=document

gives a list on how to structure the file and hints on what to do if your CSV submission fails, e.g. if leading zeroes get dropped or if NULL/0 values get replaced with nothing (as is the case with LibreOffice). The solution to those particular problems is to enter the data as a text string (by putting a leading apostrophe, for example).

That same page also recommends not working on your CSV file directly, and in particular not to re-open it in your spreadsheet program (Excel cited in that particular case) !!

Alex

#5

Hi Alex

This may well be what she needs - i.e. insert the additional \r value for formatting the line endings. I don't know and there's nothing that I can use from looking for this on the web.

The file was originally saved on a Windows machine as a *.csv dos format, and was opened in LibO which was fine (except the worksheet tab wasn't named as it had been in the original and instead labelled it merely as "Sheet 1"). Then she saved the same file from LibO using the *csv save option (because LibO doesn't give any further *csv options) and tested it by opening it up in Excel. At that point, she saw that some of the text in the file was now represented by squares, but overall the text wasn't separated out (i.e. NOT comma separated) and the fields ran together as one paragraph, including headers and content.

So it could be that LibO is not saving the DOS newline formatting, in which case I need to brief her on how she can set that up when saving in LibO and (preferably even) to automate that. I've reviewed the Calc extensions and don't see anything - so is this an opportunity for a hack perhaps?

Anyway, I hope all of this makes some kind of sense?

Cheers

#6

Hi again,

This may well be what she needs - i.e. insert the additional \r value
for formatting the line endings. I don't know and there's nothing that I
can use from looking for this on the web.

As such, this shouldn't be a problem if everything is done on Windows as
the LF/CR is usually the default for all applications that have to
insert linefeeds into files. You can sometimes run into problems when
saving from other OSes, e.g. Mac OSX or Linux, as the default linefeed
newline commands are different.

The file was originally saved on a Windows machine as a *.csv dos
format, and was opened in LibO which was fine (except the worksheet tab
wasn't named as it had been in the original and instead labelled it
merely as "Sheet 1"). Then she saved the same file from LibO using the
*csv save option (because LibO doesn't give any further *csv options)
and tested it by opening it up in Excel. At that point, she saw that
some of the text in the file was now represented by squares, but overall
the text wasn't separated out (i.e. NOT comma separated) and the fields
ran together as one paragraph, including headers and content.

This sounds pretty grim. As Tom has mentioned, select only the comma
separator for the values when you "save as" CSV, there is an option for
this in the save dialog called "Edit Filter Settings". This brings up a
dialog called "Export Text File", where you can play around with the
export filter settings. This might solve your wife's problem. If not,
then there is a possibility that the problem is indeed a bug in
LibreOffice or a missing feature in the filter export settings.

Alex

#7

Hi Tom

My wife sent me the file in question, saved on a MS machine as a CSV DOS format file.

I opened it LibO on a GNU/Linux machine and saved as a *csv file. The only change I made was to its name, I renamed it.

I sent this file back. She opened it on a MS machine using Excel and the initial problem was repeated.

When saving it in LibO, there is no option to specify any parameters as your reply suggested (although there was when I opened it in LibO). When she opens it in Excel, there is no options about parameters, nor are there any once the file is opened.

The file (MS readable) becomes MS-unreadable once it has been opened and saved in LibO (on a GNU/Linux machine) although I think also on a MS machine running LibO, so I'm not sure about the platform, but think rather that it has to do with some parameter MS DOS CSV format imposes that LibO doesn't allow for.

Cheers

#8

Hi Alex & list

I got it wrong - this was a pensions trust submission, not HMRC. My bad.

In any event - I think I may have solved this.

My test was this:

My wife sent me the file created and saved on MS as *.csv DOS. I opened it on GNU/Linux LibO. This opened a dialogue box and I ensured that the UTF-8 was the default encoding, and under the section that reads Separated By, all boxes were blanked except for comma, and that the text delimiter is ".

I edited this file, saved it under a new-name.csv and selected Edit Filter Settings in the Save As dialogue box, reviewed my selection and saved.

My wife opened this new-name.csv file in Windows Excel and it was fine ... so I guess that the changes need to be made at the front end when opening the file in LibO and then double checked at the time of saving.

This sounds like this will work for the charity concerned, so hopefully this will be the solution that satisfies the Pensions Trust as well.

Cheers all - appreciate the promptness of your replies and the quality of your help.

#9

Reading the link, I second the procedure of working on the spreadsheet format then saving as csv. I suspect they are uploading to a database using a script that expects a Windows end-of-line only (or they think it will only take a Windows end-of-line).

Try passing the spreadsheet back and forth they due a final save to csv on the Windows machine.

I have see the character encoding go wacky and have to be reset to properly import a csv into Calc on occasion. Excel may be expecting a different character set than UTF-8.

#10

Hi :slight_smile:
Good work!  Nicely done!  Congrats and thanks for letting us know :slight_smile:

That checking process is really just a quick squizz after the first time or so.  It's not something to pause and read avidly.  You kinda become aware there might be a problem even before it pops up or well after rather than when it's actually in front of you.  Either way it's fairly easy to deal with.

Mailing lists are quite variable.  You caught us at a good time with a good topic, although i guess that does happen a lot so thanks for the thanks :slight_smile:   
Regards from
Tom :slight_smile: