Exported TSV file unusable in Excel, Calc due to line shifted

1

I have tried to export TSV file out from General Ledger Transaction. The aim is to use the info in Excel and OpenOffice Calc.

Both Excel and Open Office Calc were unable to display the data correctly. And I found the reason is because the exported TSV file is shifted in the first place, hence causing the importing to shift and unusable.

1366×768 287 KB

1.) Every transaction is displayed in 1 line in Manager Software - General Ledger.

2.) But when exported, the TSV file shows the Order xxxxx is shifted to the line below.

3.) Hence when imported in Excel and Calc, both shows the Order xxx is on the next line, rather than within the same row. Further down in the data, the debit and credit figure also gets shifted, making the import poor, and unable to use the information for — analysis, addition as the figures are scattered in different columns.

So, I think there is a bug with the exporting of TSV file.

2

There is no problem for me. All is OK with LibreOffice Calc and Excel.

Screen shot from Excel…

871×479 109 KB

Manager 16.12.32.

3

I have 2 different computers running Manager 16.12.35. Both having the same issue.

It got our accounts folks stumped, and wondering how to work this out without editing the data manually.

4

I did a test with same description without a problem. Maybe some “special” character? I don’t know…

LibreOffice Calc screen shot.

894×355 56.4 KB

5

Its not going to show when its all text in the description field, it looks to me that its is combining 2 fields (description and order) on the export that causes the issue . Just my 2p :slight_smile:

6

Yes, I think it only gets shifted when item has a Description and (+) Order / PO number.

Nick, did you insert an order number to that Invoice (under PO)? Give it a try, and
maybe you will be able to reproduce the bug that I have.

7

@lokgp, it looks to me like there is something in the export that is unexpectedly being interpreted as a tab character. So the spreadsheet app moves things to the next cell/row. Not seeing your original transactions, it is impossible to know what that might be.

To resolve your problem, go back to a “good” transaction and compare it to a “bad” one. Look for where in the transaction each character in the General Ledger Transactions report is coming from. Make sure there are no leading or trailing spaces or tabs in the entry fields. Also, try substituting other text in the fields that straddle the undesired breaks. (This sounds vague because I’m describing it generically.)

8

Hi @Tut Tut, I checked. I think the issue is with the exported .tsv file itself.

As you can see from the screen shot (2nd image), the exported TSV file (eg: Order No. 319488226 … ) contains shifted data.

The “good” transaction: Contains item description but does not have Order number / PO number assigned

The “bad” transaction: Contains both item description and Order number / PO number assigned.

The length of the description (very long description / short description ) does not affect it, so that can be ruled out.

@Glider observation looks right.

“Good” & “Bad” copy and pasted from TSV file:

Revenue | Inventory - sales 31/05/2016 | 8 Step, Single Side Ladder - Winner-SS08 150.99
Revenue | Inventory - sales 01/06/2016 | 6 Step, Single Side Ladder - Winner-SS06
Order No. 319488226 106.99

As you can see, there is no special character. It looks like a “

” or break to next line has been inserted right after the description. Or a break to next line has been inserted prior to the PO / Order No.

Ideally the TSV file you be like this instead: (eg: 1 more tab spacer

Revenue | Inventory - sales 31/05/2016 | 8 Step, Single Side Ladder - Winner-SS08 150.99
Revenue | Inventory - sales 01/06/2016 | 6 Step, Single Side Ladder - Winner-SS06 Order No. 319488226 106.99

As we tried to open the file on 2 different computera, and on 2 different spreadsheet softwares. And generated TSV file from 2 different Manager software using the same “backup” with the same result, So, it does point to the TSV exported file being the cause of the issue.

Here’s the TSV file: https://drive.google.com/file/d/0B4lwR_OiFTOJWjRicUQxbHJvbVU/view?usp=sharing

9

I must point out one thing concerning your analysis of the TSV file. Remember it was not generated directly, but was assembled from transactions, so the individual cells could contain elements from several fields. So there may still be sources of “mystery” characters.

10

@Tut is right. I checked the transaction again and found “Enter” was pressed to move the Order xxxx to the next line below.

That Enter is the one that caused the issue. I tried removing the Enter, and it generated out ok.

1161×248 42.5 KB

Well, now that we have determined that… then the issue becomes why should the TSV file generated reflected the Enter. It should just take all the input and place it into 1 cell instead. We use a lot of “Enter” during the Quotation to give more details about the items. When a sale happens, we just click “Copy To” Invoice and generate the invoice. The invoice will carry over all the “Enter” pressed.

Eg:
Aluminium Ladder

  • 5 steps
  • 3.5 meter height
  • 150 kg load rating.

I think the TSV generated should reflect all the above values in 1 sentence instead. And not into 4 sentences. It is under 1 single item after all.

11

You may have entered it under one line item, but your Enter or Return character tells the computer something different. Would you have it ignore instructions? How would the program decide? If it was going to ignore your Enter character, you would not get your additional information on separate lines like you showed, which you apparently desire. You can’t have it both ways.:wink:

This all takes me back to a question I’ve had since your first post? Why are exporting this file in the first place? What are you trying to accomplish?

12

Same for me. I’m using a dutch excel. I have to do some find/replace in a text editor to get things ok.

What about an xlsx export? The format these days shouldn’t be too hard, as it’s a zipped xml file.

It’d be very very cool to be able to export 1 excel with the GL, Balance, Invoices etc. Each in a different sheet, in one go (start - end period)

13

TSV is universal for all spreadsheet programmes, xlsx is an excel format and can’t be open by all other spreadsheet programmes. Lots of Manager Users, including myself, don’t use Microsoft Office software.