Downloaded file gets all data in a single row

[ragshyd]

Hi,

I was download some data from SAP and when I open the file, it gets all the data in a single row instead of different rows. Ive tried installing the SP3 also. I am using office 2003. I dont see any change even after installing SP3.

When I download it in other systems, it is getting downloaded correctly.

Below is the link of the Excel:

http://www.yousendit.com/download/bFFPTG01Y3l6NEx2Wmc9PQ

Please help!

Rgds,
Raghu

 

[Macropod]

Hi Raghu,

The file your link refers to has an xls extension, but it's not an Excel file - it's a one-line tab-delimited text file. From what I can see, it looks like replacing every <tab>X<tab> sequence with a <tab>X<cr> sequence will produce the format you require.

 

[ragshyd]

Hi,

Thanks for your reply. Yes, It is not a Excel workbook. As mentioned, I've downloaded it from SAP, which is a text delimited file.

I can see the data imported corrected in other systems, but my system shows every thing in a single row.

Please help.

Rgds,
Raghu

 

[Derek Brown]

Have you tried 'importing' the file, instead of 'opening' the file?
Excel 2003:
Data | Import External Data | etc.
You may need to set the 'Files of type' box in the dialog box to 'All files'.
The wizard will then allow you to interpret the file correctly.
If your file has an extension of ".xls" you may need to change it to ".txt" or ".csv".

 

[Macropod]

Hi Derek,

As I observed in my previous post, Raghu's file is "a one-line tab-delimited text file". I don't see how the steps you're suggesting could correctly parse such a file. Perhaps you could provide the specific steps for the file attached to Raghu's post?

Raghu: when you say "When I download it in other systems, it is getting downloaded correctly" and "I can see the data imported corrected in other systems", what kinds of other systems do you mean? Are they able to open the file correctly in Excel? If the files are opened on those other systems using a text editor (eg Notepad) or wordprocessor (eg Word), do they have line feeds in the correct places? If so, it seems your problem may be due to some difference in the comms protocols between your system and the others to which you refer (eg when transferring the files to your system, the <cr> characters are being deleted).

 

[Derek Brown]

Apologies for the delay in replying but I had a more difficult challenge of my own to solve first.
Yes I see what you mean, Macropod - I hadn't downloaded the file to look at it in detail.
I now see that the encoding is not what I would expect in a text file being imported this way.

The 'bottom line' is that I would consider revisiting the process that downloaded the file from SAP to ensure that it is either a comma or tab delimited file in record format (e.g. with a carriage return) and perhaps (?) in ANSI format. I have not used SAP myself but I would be surprised if it does not offer suitable export for use in Excel.

If you have to do something with the file that you have, you could try the following. My assumption here is that the record format needs to be something like:
Y_HRK_ENDUSER_ALL TR SESSION_MANAGER X

I used the following steps using Excel 2003:
1. Rename a copy of "AGR_TCODES.XLS" as "AGR_TCODES.TXT".
2. Open "AGR_TCODES.TXT" in Notepad.
3. Using "Save As", change the encoding to "ANSI" and the file name to "ANSI AGR_TCODES.txt".
4. Start Excel.
5. Using "File Open", open "ANSI AGR_TCODES.txt". In the dialog box that should now appear, chose "Delimited", the 'File Origin' should show "Windows (ANSI)", and click 'Next'.
6. In the next dialog box, we want no delimiters so uncheck them all and click 'Finish'. You should now have the file in cell A1. (It seems to have a length of 12810 bytes/characters.)
7. Save the workbook as "ANSI AGR_TCODES.xls".
8. We will ignore/exclude the header for now (you can add it manually later if necessary).
9. In cell A2 enter the following formula:
=RIGHT($A$1,LEN($A$1)-130)
10. In cell A3 enter the value 5. This relates to the number of TABs in each record.
11. In cell B2 enter the following formula:
=FIND(CHAR(1),SUBSTITUTE($A$2,CHAR(9),CHAR(1),ROW(A1)*$A$3))
12. Copy-down this formula until it produces #VALUE. This should be around row 501.
13. Delete the formulas that produce #VALUE.
14. Create a new worksheet (the name in this example will be "Sheet1").
15. In cell A1 on Sheet1, enter the following formula:
=MID('ANSI AGR_TCODES'!$A$2,'ANSI AGR_TCODES'!B1+2,'ANSI AGR_TCODES'!B2-'ANSI AGR_TCODES'!B1)
The worksheet name in this formula is that create during Steps 5/6/7.
16. In cell A2 on Sheet1, enter the following formula:
=MID('ANSI AGR_TCODES'!$A$2,'ANSI AGR_TCODES'!B2+3,'ANSI AGR_TCODES'!B3-'ANSI AGR_TCODES'!B2)
The difference is that the "+2" is now "+3".
17. Copy-down this formula (from A2 so that it retains the "+3" part of the formula) until it produces #VALUE. This should be around row 500.
18. Delete the formulas that produce #VALUE.
19. Save the workbook.
19 With Sheet 1 being the active sheet, use File | Save As to save the file with the name "New ANSI AGR_TCODES.txt" as a 'Text (Tab delimited)(*.txt)" file. Click OK as we only want to save the one sheet.
20. Start Excel.
21. Use "File | Open" to open the file just saved.
22. In the dialog box, select "Delimited", file origin as "MS-DOS(PC-8) and click 'Next'.
23. In Step 2,specify the delimiters as Tab and Other (specifying other as "", that is the double-quotes). Click 'Finish'.
24. You now have the records split into columns. Delete the columns that you don't need and add headings.

Sorry that it is a long process but I wanted to avoid using VBA code if possible.