Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
does anyone have any idea how to import this spreadsheet?
I was sent an excel sheet that came from one of those $200K machines
and they expect it to be imported into an access database. the data looks like this 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 125 122 120 123 111 BOB2 0 0 0 0 0 BOB3 0 0 0 0 -0.1I just a small part this is 40 variables over many hours. but what I need is name date time value varname=bob1 date=10/17/2011 time=4:24 value=125 varname2=BOB2 date2=10/17/2011 time2=4:24 value=0 how the heck can I get the data from this? I told them this looks like a report not data. Can you get it out as a csv. I got the typical glassy stare. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
does anyone have any idea how to import this spreadsheet?
I did try transpose and got this
10/17/2011 BOB1 BOB2 BOB3 3:20:27 3:20:42 3:20:57 3:21:12 3:21:28 3:21:43 3:22:01 but the original sheet goest to cell IV (surprise) and then wraps making it 9 sections of the same data with 1 blank line between each section. this stuff spans over 3 days. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
does anyone have any idea how to import this spreadsheet?
hi sparks,
on this sheet, is there a single line with the date and time or is that there are others ? -- isabelle Le 2011-10-20 14:04, sparks a écrit : I was sent an excel sheet that came from one of those $200K machines and they expect it to be imported into an access database. the data looks like this 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 125 122 120 123 111 BOB2 0 0 0 0 0 BOB3 0 0 0 0 -0.1I just a small part this is 40 variables over many hours. but what I need is name date time value varname=bob1 date=10/17/2011 time=4:24 value=125 varname2=BOB2 date2=10/17/2011 time2=4:24 value=0 how the heck can I get the data from this? I told them this looks like a report not data. Can you get it out as a csv. I got the typical glassy stare. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
does anyone have any idea how to import this spreadsheet?
sparks wrote on 10/20/2011 :
I was sent an excel sheet that came from one of those $200K machines and they expect it to be imported into an access database. the data looks like this 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 125 122 120 123 111 BOB2 0 0 0 0 0 BOB3 0 0 0 0 -0.1I just a small part this is 40 variables over many hours. but what I need is name date time value varname=bob1 date=10/17/2011 time=4:24 value=125 varname2=BOB2 date2=10/17/2011 time2=4:24 value=0 how the heck can I get the data from this? I told them this looks like a report not data. Can you get it out as a csv. I got the typical glassy stare. If the file IS a CSV and not XLS[?] then you have 2 options; import as CSV into Access (probably the easiest), -OR- read the file into an array and write it to the access database using ADO. If it is XLS[?] then you can do the latter of the above as I'm not sure if Access can import from an Excel file directly, but you could do it in Access via VBA using ADO. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
does anyone have any idea how to import this spreadsheet?
This is what is weird...I guess its backwards compatibility or
something. the file is an xlsx but it is wrapping at IV I guess for excel 97 so we checked and it has about 8000 time values...can the newer excel go that high? we end up with a single sheet that looks like this (quick cut and paste not what we have) over and over until the end of its time cycle. 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 125 122 120 123 111 BOB2 0 0 0 0 0 BOB3 0 0 0 0 -0.1I 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 125 122 120 123 111 BOB2 0 0 0 0 0 BOB3 0 0 0 0 -0.1I 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 125 122 120 123 111 BOB2 0 0 0 0 0 BOB3 0 0 0 0 -0.1I IF it was a single row of data a quick transpose and we are done. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
does anyone have any idea how to import this spreadsheet?
On Oct 21, 5:56*am, sparks wrote:
This is what is weird...I guess its backwards compatibility or something. the file is an xlsx but it is wrapping at IV I guess for excel 97 so we checked and it has about 8000 time values...can the newer excel go that high? we end up with a single sheet that looks like this (quick cut and paste not what we have) over and over until the end of its time cycle. 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 * * * 125 * * 122 * * 120 * * 123 * * 111 BOB2 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 BOB3 * * * * * * * * * *0 *0 * * * 0 * * * 0 * * * -0.1I 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 * * * 125 * * 122 * * 120 * * 123 * * 111 BOB2 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 BOB3 * * * * * * * * * *0 *0 * * * 0 * * * 0 * * * -0.1I 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 * * * 125 * * 122 * * 120 * * 123 * * 111 BOB2 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 BOB3 * * * * * * * * * *0 *0 * * * 0 * * * 0 * * * -0.1I IF it was a single row of data a quick transpose and we are done. Copy your excel sheet into notepad. Then in excel, import the data from text. Don't try to copy and paste. Use the data import function which is under the data tab. Then once the import begins, import as "delimited", using a space for the delimiter. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
does anyone have any idea how to import this spreadsheet?
I put it into notepad.
did the import and it is still putting it into the same format I put into notepad. it is wrapping at IV....the same 255 limit i had in the originail file. will try some more. On Fri, 21 Oct 2011 05:17:35 -0700 (PDT), Andrew wrote: On Oct 21, 5:56Â*am, sparks wrote: This is what is weird...I guess its backwards compatibility or something. the file is an xlsx but it is wrapping at IV I guess for excel 97 so we checked and it has about 8000 time values...can the newer excel go that high? we end up with a single sheet that looks like this (quick cut and paste not what we have) over and over until the end of its time cycle. 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 Â* Â* Â* 125 Â* Â* 122 Â* Â* 120 Â* Â* 123 Â* Â* 111 BOB2 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 BOB3 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*0 Â*0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* -0.1I 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 Â* Â* Â* 125 Â* Â* 122 Â* Â* 120 Â* Â* 123 Â* Â* 111 BOB2 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 BOB3 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*0 Â*0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* -0.1I 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 Â* Â* Â* 125 Â* Â* 122 Â* Â* 120 Â* Â* 123 Â* Â* 111 BOB2 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 BOB3 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*0 Â*0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* -0.1I IF it was a single row of data a quick transpose and we are done. Copy your excel sheet into notepad. Then in excel, import the data from text. Don't try to copy and paste. Use the data import function which is under the data tab. Then once the import begins, import as "delimited", using a space for the delimiter. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
does anyone have any idea how to import this spreadsheet?
hi Sparks,
Here is an example, check that the dates are in a valid date format for your xl version. http://cjoint.com/?AJvuvWGxtt4 Sub Macro1() For i = 1 To Sheets("sh1").Range("A65536").End(xlUp).Row If Application.IsText(Sheets("sh1").Range("A" & i)) Then For y = 2 To 6 With Sheets("sh2") n = .Range("A65536").End(xlUp).Row + 1 .Range("A" & n + y) = "varname = " & Sheets("sh1").Cells(i, 1) .Range("A" & n + y + 1) = "date = " & dt .Range("A" & n + y + 2) = "time = " & Format(Sheets("sh1").Cells(rw, y), "hh:mm:ss") .Range("A" & n + y + 3) = "value = " & Sheets("sh1").Cells(i, y) End With Next Else If IsDate(Sheets("sh1").Range("A" & i)) Then dt = Sheets("sh1").Range("A" & i) rw = i End If End If Next End Sub -- isabelle |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
does anyone have any idea how to import this spreadsheet?
On 22/10/2011 1:29 AM, sparks wrote:
I put it into notepad. did the import and it is still putting it into the same format I put into notepad. it is wrapping at IV....the same 255 limit i had in the originail file. will try some more. Try a programmers editor such as power edit instead then check at the 255 character limit for a special character that may be causing it to wrap such as a soft return. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
does anyone have any idea how to import this spreadsheet?
On Oct 21, 4:40*pm, XR8 Sprintless wrote:
On 22/10/2011 1:29 AM, sparks wrote: I put it into notepad. did the import and it is still putting it into the same format I put into notepad. it is wrapping at IV....the same 255 limit i had in the originail file. will try some more. Try a programmers editor such as power edit instead then check at the 255 character limit for a special character that may be causing it to wrap such as a soft return. When you import from notepad, you have to make sure to import as Delimited (as opposed to fixed width). Then you have to be sure you select the "space" character as the delimiter. If you did that and it didn't work, then maybe it won't work. I use that method all the time for importing tables from pdf files. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
does anyone have any idea how to import this spreadsheet?
On Fri, 21 Oct 2011 10:29:43 -0500, sparks wrote:
I put it into notepad. did the import and it is still putting it into the same format I put into notepad. it is wrapping at IV....the same 255 limit i had in the originail file. will try some more. On Fri, 21 Oct 2011 05:17:35 -0700 (PDT), Andrew wrote: On Oct 21, 5:56Â*am, sparks wrote: This is what is weird...I guess its backwards compatibility or something. the file is an xlsx but it is wrapping at IV I guess for excel 97 so we checked and it has about 8000 time values...can the newer excel go that high? we end up with a single sheet that looks like this (quick cut and paste not what we have) over and over until the end of its time cycle. 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 Â* Â* Â* 125 Â* Â* 122 Â* Â* 120 Â* Â* 123 Â* Â* 111 BOB2 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 BOB3 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*0 Â*0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* -0.1I 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 Â* Â* Â* 125 Â* Â* 122 Â* Â* 120 Â* Â* 123 Â* Â* 111 BOB2 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 BOB3 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*0 Â*0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* -0.1I 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 Â* Â* Â* 125 Â* Â* 122 Â* Â* 120 Â* Â* 123 Â* Â* 111 BOB2 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* 0 BOB3 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*0 Â*0 Â* Â* Â* 0 Â* Â* Â* 0 Â* Â* Â* -0.1I IF it was a single row of data a quick transpose and we are done. Copy your excel sheet into notepad. Then in excel, import the data from text. Don't try to copy and paste. Use the data import function which is under the data tab. Then once the import begins, import as "delimited", using a space for the delimiter. I tried what isabelle posted and this is what I get. varname = BOB1 date = 10/17/2011 time = 04:24:20 value = 125 varname = BOB1 date = 10/17/2011 time = 04:24:35 value = 122 varname = BOB2 date = 10/17/2011 time = 04:24:20 value = 0 varname = BOB2 date = 10/17/2011 time = 04:24:35 value = 0 not sure why it is reading 2 entries or the blank lines. ============= I tried the notepad but it still sees the wrap at 255. I told them I needed data that was NOT word wrapped like this. They are blaming the program for the data saying its backwards compatible for older versions. I told them get me the data or convert it yourself. Dont know how far that is going to go. they push a button and get it and then its my fault. Typical |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
does anyone have any idea how to import this spreadsheet?
On Oct 25, 7:17*am, sparks wrote:
On Fri, 21 Oct 2011 10:29:43 -0500, sparks wrote: I put it into notepad. did the import and it is still putting it into the same format I put into notepad. it is wrapping at IV....the same 255 limit i had in the originail file. will try some more. On Fri, 21 Oct 2011 05:17:35 -0700 (PDT), Andrew wrote: On Oct 21, 5:56*am, sparks wrote: This is what is weird...I guess its backwards compatibility or something. the file is an xlsx but it is wrapping at IV I guess for excel 97 so we checked and it has about 8000 time values...can the newer excel go that high? we end up with a single sheet that looks like this (quick cut and paste not what we have) over and over until the end of its time cycle.. 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 * * * 125 * * 122 * * 120 * * 123 * * 111 BOB2 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 BOB3 * * * * * * * * * *0 *0 * * * 0 * * * 0 * * * -0.1I 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 * * * 125 * * 122 * * 120 * * 123 * * 111 BOB2 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 BOB3 * * * * * * * * * *0 *0 * * * 0 * * * 0 * * * -0.1I 10/17/2011 4:24:20 4:24:35 4:24:50 4:25:05 4:25:20 BOB1 * * * 125 * * 122 * * 120 * * 123 * * 111 BOB2 * * * 0 * * * 0 * * * 0 * * * 0 * * * 0 BOB3 * * * * * * * * * *0 *0 * * * 0 * * * 0 * * * -0.1I IF it was a single row of data a quick transpose and we are done. Copy your excel sheet into notepad. *Then in excel, import the data from text. *Don't try to copy and paste. *Use the data import function which is under the data tab. *Then once the import begins, import as "delimited", using a space for the delimiter. I tried what isabelle posted and this is what I get. varname = BOB1 date = 10/17/2011 time = 04:24:20 value = 125 varname = *BOB1 date = 10/17/2011 time = 04:24:35 value = 122 varname = BOB2 date = 10/17/2011 time = 04:24:20 value = 0 varname = BOB2 date = 10/17/2011 time = 04:24:35 value = 0 not sure why it is reading 2 entries or the blank lines. ============= I tried the notepad but it still sees the wrap at 255. I told them I needed data that was NOT word wrapped like this. They are blaming the program for the data saying its backwards compatible for older versions. I told them get me the data or convert it yourself. Dont know how far that is going to go. they push a button and get it and then its my fault. Typical Would you mind sending the data. I'd like to take a look at it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Import from spreadsheet | Excel Programming | |||
Import Spreadsheet into Access | Excel Worksheet Functions | |||
Import an excel spreadsheet in ASP.NET | Excel Programming | |||
Import from spreadsheet | Excel Programming | |||
Import a spreadsheet | Excel Programming |