[Solved] Help with Tab Delimated

Discuss the spreadsheet application

[Solved] Help with Tab Delimated

Postby Tel Streaky » Tue Jan 14, 2014 1:13 pm

Hi there,

I'm a bit of a novice when it comes to these things but I've run into a strange problem. For my shop I download a csv file to deal with my orders, and I've been doing this for over a month with no problems. Then yesterday I downloaded and edited my file as usual and then the posting site I use didn't recognise the different columns of my work sheet (all the data had been put into the first column and was no longer separated by tabs). I hadn't changed any settings that I was aware of and the tab delimited was still checked (indeed when I look at the sheet it still looks fine). I tried sending my file to the posting site and they sent it back to me and as soon as I opened it the same thing happened again. I checked old shipping files and they uploaded fine but as soon as I copied one line from the recent file into an old sheet the same thing happened.

I'm not sure if I've explained things too well there but can anyone help me? I'm just confused and need to do my orders for today and still have the same problem.

Many thanks!
Last edited by Hagar Delest on Tue Jan 14, 2014 10:04 pm, edited 1 time in total.
Reason: tagged [Solved].
Apache Open Office 4.0.1
Tel Streaky
 
Posts: 7
Joined: Tue Jan 14, 2014 1:05 pm

Re: Help with Tab Delimated

Postby Villeroy » Tue Jan 14, 2014 1:39 pm

If you are running Windows, http://csved.sjfrancke.nl/ is the program you should use to edit text files. It is a tiny little program made for these types of files.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27301
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Help with Tab Delimated

Postby acknak » Tue Jan 14, 2014 1:57 pm

Hi and welcome to the community forum!

Sorry, I'm not sure I understand what you're doing.

How are you editing the csv file? Do you use Calc for that?

If so, then you really ought to take a look at Villeroy's suggestion. Using Calc as an editor can lead to strange problems, just like you're seeing now.

If you really want to stick with Calc, then I think we'll need to see a sample of the files you're working with--maybe an old one that works and a new one that doesn't.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Help with Tab Delimated

Postby Tel Streaky » Tue Jan 14, 2014 2:39 pm

Hi guys, thanks so much for helping. Thig is I've used calc for nearly a year now without any problems, I'm not editing so much as just cutting and pasting an uploaded csv file into calc and then putting that into a posting programme.

I've attached to docs but I've had to take out a lot of sensitive customer information for obvious reasons. Do they help at all? The earlier date was afile that was working (not sure it will now after I removed the sensitive columns). I just need it so that when I upload the file to the posting programme it recognises the different columns (so it can read name, address line 1, shipping method etc) instead of al data being put into one coloumn
Apache Open Office 4.0.1
Tel Streaky
 
Posts: 7
Joined: Tue Jan 14, 2014 1:05 pm

Re: Help with Tab Delimated

Postby Tel Streaky » Tue Jan 14, 2014 2:42 pm

Actually I don't think those examples uploaded, it says extension csv isn't allowed?
Apache Open Office 4.0.1
Tel Streaky
 
Posts: 7
Joined: Tue Jan 14, 2014 1:05 pm

Re: Help with Tab Delimated

Postby Villeroy » Tue Jan 14, 2014 2:54 pm

This forum software is so stupid. Simply rename the files from .csv to .zip, .odt, .ods or something.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27301
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Help with Tab Delimated

Postby Tel Streaky » Tue Jan 14, 2014 2:58 pm

Thank you, here they are
Attachments
130114v3.ods
(16.27 KiB) Downloaded 143 times
030114v2.ods
(14.3 KiB) Downloaded 64 times
Apache Open Office 4.0.1
Tel Streaky
 
Posts: 7
Joined: Tue Jan 14, 2014 1:05 pm

Re: Help with Tab Delimated

Postby acknak » Tue Jan 14, 2014 3:40 pm

I don't see any difference in the way the two files export to text/csv.

The site you're using should have a description of what they want for uploaded data--or, check one of the files that you've downloaded and follow the settings you see they're using.

My guess is that the option to quote text values has somehow been turned off. A csv file that contains text data that uses commas or quotes, such as your "v2" sample:

    Marvel Universe Whiplash, Iron Man 2 Movie 4" Figure Complete
will definitely have problems if the field is not properly delimited.

And, this is the primary reason NOT to use Calc for jobs like this: unless you are very careful, Calc WILL (at some point) mess up your csv file.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Help with Tab Delimated

Postby Tel Streaky » Tue Jan 14, 2014 3:49 pm

Ok, well I'm happy to get to grips with another programme, but for this week I really need to upload them in calc as I've got orders in and I might not be able to do it in time otherwise.

I've just had a look at the file that isn't working and when I go to text to columns the separated by comma bit is no longer checked, and if I check it and save it it doesn't seem to save. Might this be the problem? If so how do I get it to save correctly?
Apache Open Office 4.0.1
Tel Streaky
 
Posts: 7
Joined: Tue Jan 14, 2014 1:05 pm

Re: Help with Tab Delimated

Postby Tel Streaky » Tue Jan 14, 2014 5:06 pm

This is driving me crazy! It appears that when I save the file it's not recognising the separated by comma. Even if I open it with the program csved mentioned above it still has all the data in one column, but when I open it with calc they are all sepatated. Please can someone help? I can't think of anything I've changed and as I said it's been working fine for the past 12 months
Apache Open Office 4.0.1
Tel Streaky
 
Posts: 7
Joined: Tue Jan 14, 2014 1:05 pm

Re: Help with Tab Delimated

Postby Tel Streaky » Tue Jan 14, 2014 5:40 pm

Unbelievable. 24 hours that has taken me, 24 hours to realise that when exporting the csv file it's not tab and comma that need to be checked, it's just comma. That was the problem all along, so simple, I will now be having a large drink.
Apache Open Office 4.0.1
Tel Streaky
 
Posts: 7
Joined: Tue Jan 14, 2014 1:05 pm

Re: Help with Tab Delimated

Postby Villeroy » Tue Jan 14, 2014 5:54 pm

Many people use spreadsheets without having a clue about the tool they are using. Of course you can import csv files and do whatever a spreadsheet offers to do with the imported data. But almost nobody does any spreadsheet tasks with spreadsheets. For instance, we rarely see any math related questions on this forum. People who do math with spreadsheets are either rare or they do not have any problems because this is what spreadsheets do. About 90% of all Calc issues on this forum are about text (text cells in text tables, text matching, text storage, text files).
This is why I recommended a dedicated small and simple tool to open, sort, filter and edit csv files.

Anyhow. You uploaded 2 spreadsheet documents which are the results of your effors to import text files into this calculation tool. What we would like to see are the actual text files before you saved them as spreadsheet files. Select a .csv file in your file manager. Do not open the file. Rename that file from 030114v2.csv to 030114v2.zip or 030114v2.ods. Renaming a file just attaches another label to the file without changing a single byte of file content.
Alternatively, Right-click a .csv file and choose OpenWith... If you are running WIndows, there should be a most simple editor on board named "Notepad" which simply reads lines of text files. Copy just a few lines of the file and paste them into the forum.

From the 2 spreadsheets you uploaded, 130114v3.ods has a flaw.
030114v2.ods has a first row of column labels (A1:AG1) and data from A2 to AG16. 139114v3.ods is somewhat frazzled with data that slipped into the wrong column (rows 6,14,15,17 and 23). The problem starts in column E where the quantity field gets text values.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27301
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Help with Tab Delimated

Postby Villeroy » Tue Jan 14, 2014 5:55 pm

Tel Streaky wrote:Unbelievable. 24 hours that has taken me, 24 hours to realise that when exporting the csv file it's not tab and comma that need to be checked, it's just comma. That was the problem all along, so simple, I will now be having a large drink.

Cheers.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27301
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 41 guests