Lost in Excel

[SolarMusher]

Hi all,
Please don't laugh.
I've exported data from loc app and I'm wondering what I could do to make it readable in excel.
I've to confess I'm not an excel expert to say the least.
Actually I've an excel spreadsheet with all datas in one row and (absolutely) no idea on how to have it friendly  .
Could someone (younger than me) help/educate me to fill that gap?
Thanks,
Erik

[TomW]

Musher;

You may have to tell it what separators the file is using.

Usually tabs commas or other characters.

I use LibreOffice and it asks and offers options I just accept its idea of what to use (commas I think).

Never ever used Excel so can't help with it.

Tom

[plongson]

Hey Erik,
The most I do just to get things in order is select the little box above line "1" and to the left of "A" to select all then auto fit the column width find the little triangle under "Format". Then I select column "A" and "Sort" and select "Newest to Oldest" and "Expand the Selection".

This helps but then all my data is junk . I have no clue why it recorded what it did. I wish there was an (easy) way to flush all this and have it start anew. Here are some screen shots...

What does your data look like??

Paul

[SolarMusher]

Hey Paul, you're a friend!
My datas look like a piece of scrap  .
I've attached CSV sheet below.
I'm going to try what you said... if I could find it 

You may have to tell it what separators the file is using.

Tom,
How do you do that?
I'm a real excel dummy!
Erik

[SolarMusher]

Musher;

You may have to tell it what separators the file is using.

Usually tabs commas or other characters.

I use LibreOffice and it asks and offers options I just accept its idea of what to use (commas I think).

Never ever used Excel so can't help with it.

Tom

Tom, all datas are separated with commas so I don't understand why these datas are all in on row.
I believe I've Office 2003 installed on Win7.
Erik

[TomW]

Musher;

You may have to tell it what separators the file is using.

Usually tabs commas or other characters.

I use LibreOffice and it asks and offers options I just accept its idea of what to use (commas I think).

Never ever used Excel so can't help with it.

Tom

Tom, all datas are separated with commas so I don't understand why these datas are all in on row.
I believe I've Office 2003 installed on Win7.
Erik
Erik

Erik;

Sorry, I have never used Excel so do not have a clue.

Tom

[Lya72]

Hi Erik,

Try this (I used that for old versions of Excel) :

- Rename your export file with a .txt extension

- Then, Open it with Excel and a wizard will popup where you can specify the separator (colon or semi-colon)

- After, you will have to insert a column before the "TimeStamp" column and put the formula in each row to convert from timestamp notation to date.
=(A1/1000 + <timezone Offset>*60*60)/86400 + 25569

You must adjust the bold mentions :
For me, now in Paris, <timezone Offset> = 1 so the formula will be =(B6/1000 +1*60*60)/86400 + 25569
If you do an incremental copy with the plus in the cell corner, the reference D2, will automatically adjust for each row (D3,D4,D5......)


- To finish, don't forget to apply a date format on this new column


Yann

PS : to know the version of the Windows software, go to the ? menu and select About item.

[Resthome]

Hey Paul, you're a friend!
My datas look like a piece of scrap  .
I've attached CSV sheet below.
I'm going to try what you said... if I could find it 

You may have to tell it what separators the file is using.

Tom,
How do you do that?
I'm a real excel dummy!
Erik

Erik..
Take a look at this Excel file of your data. Did you just do a straight Export from Offline Data or was the file changed?
John

[Westbranch]

Erik, HELP is your friend, upper left corner of Excel

here is a sample:

ALL SITES|TRUSTED SITES
Import or export text (.txt or .csv) files Article To import a text file, you can open the file or import the text file as an external data range. To export a text file, use the Save As command

Eric

[SolarMusher]

Erik..
Take a look at this Excel file of your data. Did you just do a straight Export from Offline Data or was the file changed?
John

Hey John,
How did you do that? I want to buy the same 
I just have clicked on the loc app export button and it asked me where I want to save it and it saved it as a CSV file that I have opened with Excel. What should I do? Save it in Excel file directly? Open it with another program?
John don't laugh, I really do my best which is very limited

Erik, HELP is your friend, upper left corner of Excel

here is a sample:

ALL SITES|TRUSTED SITES
Import or export text (.txt or .csv) files Article To import a text file, you can open the file or import the text file as an external data range. To export a text file, use the Save As command

Hi Eric,
Windows help has never been my friend  Google is better.
I'm going to give it a try another time.
Erik

[Westbranch]

Erik,  If it asks you  just save it to ""YOUR/MY DOCUMENTS"  then you can quickly find it. 

As Yann said  change CSV to TXT and it will open easily

ADD: DID you change the file type at the bottom of the page, when you tried to open it?

If not, it will be looking for an XL type file.  Change the type to TEXT type (.txt , CSV etc)

[SolarMusher]

Erik,  If it asks you  just save it to ""YOUR/MY DOCUMENTS"  then you can quickly find it. 

As Yann said  change CSV to TXT and it will open easily

ADD: DID you change the file type at the bottom of the page, when you tried to open it?

If not, it will be looking for an XL type file.  Change the type to TEXT type (.txt , CSV etc

Eric,
I've saved it text file and then opened it in excel, as Yann said Win asked a "few things" and after I've clicked everywhere I saw... datas in rows, great! Not very easy, but better than before.
See it below.
What is the program that you use to open CSV directly and have it in the right order?
Many thanks to all,
Erik

[Westbranch]

I use Excel, but i don't change to TXT. a bit more awkward if you are not familiar with EXCEL.

to improve it highlight all the columns you are using, left click and hold down, drag mouse to  right and you will see the column be highlited. Stop when you reach the last one. Now Right mouse click, pop down screen appears, go to column width and click, with left button. enter any number you want I used 15 and observe the columns change. You can also do this one column at a time.

hth

[SolarMusher]

Thanks for your help Eric, very appreciated.
I've found that for those who could have the same problem, except that you couldn't change .CSV to .text, you will have to open CSV in excel ans save it as .text and then ask Excel to open this text file. See #3.

1. Navigate to the csv file on your server. In Firefox right click anywhere on the page and choose "Save Page As".

2. To force Excel to let us specify the format, manually change the file extension from "csv" to "txt" in the Save As window. Then save the .txt file to your hard drive.

3. Now go to Excel, then open the .txt file from within Excel. (You will probably need to change "Files of type" manually to All Files in order to see the .txt file in Excel's open dialogue.) The .txt extension on the csv file forces Excel to initiate its Text Import Wizard.

4. In the Text Import Wizard Step 1 of 3 specify "Original data type" as "Delimited" and make sure that "Start import at row:" is "1" and "File origin:" is "437 : OEM United States" are selected.

5. In the Text Import Wizard Step 2, ensure only "Comma" is checked under "Delimiters".

6. In Step 3, in the "Data preview" make sure the first column is highlighted. Then scroll to the last column on the right and while holding down shift, click the column with your mouse pointer. All columns will then be highlighted. Under "Column data format" select "Text". Then click "Finish".

7. Now the file will open in Excel with the data exactly as entered by the form user.

Erik

[Resthome]

Erik..
Take a look at this Excel file of your data. Did you just do a straight Export from Offline Data or was the file changed?
John

Hey John,
How did you do that? I want to buy the same 
I just have clicked on the loc app export button and it asked me where I want to save it and it saved it as a CSV file that I have opened with Excel. What should I do? Save it in Excel file directly? Open it with another program?
John don't laugh, I really do my best which is very limited

Erik.. It depends on what version of Excel you are using. I'm using 2010 and the CSV opens and is formated without doing much of anything. Not sure what it does with 2007 as I do not have a copy on my laptop. I have it on another machine at home but will not be there for a few days. Prior to Excel 2007 all bets are off in how it handles commas as the delimiter by default.
John