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
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
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
(http://i1199.photobucket.com/albums/aa471/plongson/junk/MNspred_zps9aa024ca.png)
(http://i1199.photobucket.com/albums/aa471/plongson/junk/MNspred1_zpsf0318f94.png)
Hey Paul, you're a friend!
My datas look like a piece of scrap ;D.
I've attached CSV sheet below.
I'm going to try what you said... if I could find it :P
QuoteYou may have to tell it what separators the file is using.
Tom,
How do you do that?
I'm a real excel dummy!
Erik
QuoteMusher;
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
Quote from: SolarMusher on February 24, 2013, 10:36:39 AM
QuoteMusher;
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
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.
Quote from: SolarMusher on February 24, 2013, 10:16:29 AM
Hey Paul, you're a friend!
My datas look like a piece of scrap ;D .
I've attached CSV sheet below.
I'm going to try what you said... if I could find it :P
QuoteYou 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
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
QuoteErik..
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 ;D
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 ???
QuoteErik, 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 ;D Google is better.
I'm going to give it a try another time.
Erik
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)
QuoteErik, 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
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
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.
Quote1. 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
Quote from: SolarMusher on February 24, 2013, 04:52:03 PM
QuoteErik..
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 ;D
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
John, Erik, from memory prior issues had a little pop up window that asked you how the information was delimited (CSV = comma/character separated values) as it also could be a period or ?
you just had to click on whatever the file used and voila.. off it went, at the same time IIRC you could set the column width and a few other things...
cheers
sunny at 10, huge wet flakes now... darn
John & Eric,
You must be right, I've Office 2003 and obviously it's not up to date :P
I had found Word Office expensive and was naively thinking that it would be equivalent to the latest version. Not sure, I will buy the new one, Bill is rich enough! I still need to pay off my old 2003 version so I could live with it ;D, at worst I've found a free CSV editor better than Excel: http://www.windows7download.com/win7-ron-s-editor/download-lcqefgxx.html.
Thanks guys!
Erik
Erik;
You might look and see if Libre Office is available for your Operating System. It is a fairly complete office system.
That is what I use. Free, current and just works.
Just a thought
Tom
QuoteErik;
You might look and see if Libre Office is available for your Operating System. It is a fairly complete office system.
That is what I use. Free, current and just works.
Just a thought
Tom
Yes, but a good thought, Tom. I think I'm going to look at Libre Office closer.
Thanks a lot!
Erik
i'm a bit late chiming into this and didn't read the whole thread so if i'm misreading what's wanted wrongly then please forgive me. i have been using open office and is a free download for my pc using vista. not sure of your os, but take a look.
Quote from: niel on February 25, 2013, 08:41:07 PM
i'm a bit late chiming into this and didn't read the whole thread so if i'm misreading what's wanted wrongly then please forgive me. i have been using open office and is a free download for my pc using vista. not sure of your os, but take a look.
Niel;
I thought Open Office got rolled into Libre Office or something? I used O-O for years on Debian systems then when I started using Ubuntu it had Libre Office on it?
Either probably fine.
Tom
they both are competition for Micro-bloat-ware (MS). I have used both over the years. Here is a comparison of both, just minor differences. http://office-suites.venturebeat.com/compare/3-16/OpenOffice-org-vs-LibreOffice
Erik, is it working for you?
Hey Eric,
Yes, no problem it's working fine now. Find these data sheet very interesting, especially when I can watch globally since november how few days I have had where my batteries have reached float stage. Waiting feverishly for a better production in March... and for a higher SG, still have a steady 1.250/55 after 4hrs EQ at 64V. I wonder if one day I could reach 1.275 in winter even with a 4kwhr PV array ;D
Erik