does anyone have any idea how to import this spreadsheet?

  • Thread starter Thread starter sparks
  • Start date Start date
S

sparks

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.
 
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.
 
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 :
 
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.
Click to expand...

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.
 
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
Click to expand...
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
Click to expand...
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
Click to expand...

IF it was a single row of data a quick transpose and we are done.
 
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.


IF it was a single row of data a quick transpose and we are done.
Click to expand...

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 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.
 
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
 
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.
Click to expand...

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.
 
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.
Click to expand...

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.
 
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.
Click to expand...


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
 
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
Click to expand...

Would you mind sending the data. I'd like to take a look at it.
 
Back
Top