Solved

How do I import data from excel, where cells contain ALT ENTER for carriage returns

Posted on 2004-08-13
20
524 Views
Last Modified: 2012-08-14
Hello,

I have a spreadsheet which I need to import into Access.  The data in one column is populated using alt enter to get a new line.  I need each 'line' in those cells in a seperate record.

Data in Spreadsheet

Cell A5: Drawing#           12345
Cell B5: Drawing Title      Large Tank
Cell C5: Issue Dates         4/7/04, rev A
                                     5/9/04, rev B
                                     8/14/04, rev C

I need in Access
fldDrawingNum       fldIssueDate    fldIssueRev
12345                     4/7/04            A
12345                     5/9/04            B
12345                     8/14/04          C

Many thanks

Bryan
0
Comment
Question by:BryanEaton
  • 10
  • 6
  • 4
20 Comments
 
LVL 8

Expert Comment

by:Eric Flamm
Comment Utility
Import the data into a text field, then parse it (Alt-Enter is a line feed character, Chr(10))

-ef
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
One Solution:
To import data from Excel into Access, each column has to have the same data type. With some Excel wizardry, you are going to have to put Column titles  - RevA, RevB, RevC, etc. and move the dates under their respective column titles.  I realize there will be a different number of Revs per drawing. You must determine the max rev level and create column headings to that level.

However, to get the data in the form you want in your table you had best convert the data in Excel.  Create two new Excel columns using Excel wizardry, IssueDate and RevLevel.  Insert lines for each rev level above A, and using string manipulation, insert the correct date and rev leve in each new line.  Fill in the drawing number where blank, delete the column with multiple lines per cell, and then import.


0
 
LVL 8

Expert Comment

by:Eric Flamm
Comment Utility
To be more specific...

I would import the data into an Access table (call it tblImport) - you'd have 1 record for each row in Excel.
Set up another table in Access (call it tblResults) using the fields you listed.

In a procedure, open a recordset on tblImport, and loop through the multiline field looking for CHR(10); each time you find one, write a record to tblResults. The code wouldlook something like:

dim db as database
dim rstImport as Recordset
dim rstResults as Recordset
dim strMulti as String
dim strLine as String

set db=CurrentDB
set rstImport=db.openrecordset(tblImport)
set rstResults=db.openrecordset(tblResults)

rstimport.movefirst
while not rstimport.eof
   strMulti=rstImport("Field3")
   do until instr(strMulti,chr(10))=0
    rstResults.AddNew
    rstResults("fldDrawingNum")=rstImport("Field1")
    strLine=left(strMulti,instr(strMulti,chr(10))-1)
    strMulti=mid(strMulti,instr(strMulti, chr(10))+1)
    rstResults("fldIssueDate")=left(strLine,instr(strLine,",")-1)
    rstResults("fldIssueRev")=mid(strLine,instr(strLine,",")+1)
    rstresults.update
   loop
rstImport.movenext
Wend



   
0
 
LVL 8

Expert Comment

by:Eric Flamm
Comment Utility
hmmm-I don't think that will get the last entry in each row (because the last row doesn't have CHR(10)) in it.

Try:

Do until InStr(strMulti,",")=0

-ef
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 75 total points
Comment Utility
Add the following to eflamm's solution:

Dim arrMulti() as string

in place of strMulti
arrMulti =  split(rstImport("Field3"))

Now you have something you can work with.


0
 
LVL 8

Assisted Solution

by:Eric Flamm
Eric Flamm earned 425 total points
Comment Utility
GrayL - shouldn't that be split(rstImport("Field3"),chr(10)) - since Split assumes space as the delimiter, where in this case it's Chr(10)?

-ef
0
 
LVL 8

Accepted Solution

by:
Eric Flamm earned 425 total points
Comment Utility
You still have to loop through the items and break up the date and the revision, although I suppose you could use split there, too.

dim db as database
dim rstImport as Recordset
dim rstResults as Recordset
dim strMulti() as string
dim strLine() as String
dim i as integer

set db=CurrentDB
set rstImport=db.openrecordset(tblImport)
set rstResults=db.openrecordset(tblResults)

rstimport.movefirst
while not rstimport.eof
   strMulti=Split(rstImport("Field3"),chr(10))
   i=0
   on error goto NextRecord
    Do
    strLine=Split(strMulti(i),",")         'This line causes a subscript out of range error when we're done with the record
    rstResults.AddNew
    rstResults("fldDrawingNum")=rstImport("Field1")
    rstResults("fldIssueDate")=strLine(0)
    rstResults("fldIssueRev")=right(strLine(1),1)  'We really only want the Rev letter, right?
    rstresults.update
   i=i+1             'Get next revision record
   loop
NextRecord:
rstImport.movenext
Wend

-ef
0
 

Author Comment

by:BryanEaton
Comment Utility
Thanks guys,

The data has approx 500 rows of data and from 1 to 23 lines in the 'magic' cell.  So doing a split in Excel would be prohibitive.  I tried it and it takes way too long manually.  Although the way the data is entered, I need to do some post import manipulation anyway.

I havnt tried it out yet - under pressure for other things right now, but I shall get back to it Monday.
Does it make a difference that I am only using Access 2000?

Thanks again

Bryan
0
 
LVL 8

Expert Comment

by:Eric Flamm
Comment Utility
No, the Split function has been around for a while. Might need to set a reference in VBA to DAO (Data Access Objects, not ADO) - that's where the Database and Recordset objects live.

-ef
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Eflamm:  Yeah - you're so right. I think you've got it now. Good work.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:BryanEaton
Comment Utility
Hi Guys,

I am still working on getting the data over into Access.  I seem to need to use memo fields to hold the data I am reading from.  The longest cell is 861 charactors long.  Will this affect the code at all?

Cheers

Bryan
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Access claims the memo field can hold up to 65,535 bytes, but only 255 in a text field.
0
 

Author Comment

by:BryanEaton
Comment Utility
Hello,

OK, I have now got the data over into Access from Excel.  I have successfully run through importing 1 record, then I get the subscript out of range issue and it stops.  I am working on it...

Also, I am trying to extract much more than the rev and date, but the only really consistant thing about the multi line input is the date is first, seperated by a comma.  The rest has some consistancy, so I am taking that into a 'result' field, then will use some cunning instr() and mids() and lefts() etc to get the data into the appropriate fields.  I figured that if I was shown how to get over the initial sticking point, I could go on from there.

Thanks for getting me this far though, it really helps.  I will get back when I have re read your advice, to see what I have missed about this subscript error thing...

Bryan
0
 

Author Comment

by:BryanEaton
Comment Utility
I get to record 2 actually... then it bombs out on me with the subscript thing.  I have tried deleting the first 2 records, but it still does it at the same point, after record 2.  Do you have any suggestions?

I am now only interested in extracting the date (up to the first comma, or the first 9 characters as it is pretty standard at dd-mmm-yy) then the rest of the line into a general text field which I can do subsequent processing on (as the rest of it is very inconsistant).

Thanks

Bryan
0
 
LVL 8

Assisted Solution

by:Eric Flamm
Eric Flamm earned 425 total points
Comment Utility
You might try adding Erase strLine and Erase strMulti inside the loop, after you've written out the new record. The split function returns a "zero-based, one-dimensional array" according to VB Help - I assume it also sets the upper bound for the array, so if the next complex field has more delimiters - chr(10) or "," or whatever - it will generate too many elements and go out of range. The Erase statement resets the upper bound of a dynamic array to unknown, so it can be set dynamically by the Split function.

-ef
0
 
LVL 8

Expert Comment

by:Eric Flamm
Comment Utility
Bryan,

Did you get this to work? If not, what's happening now?

-ef
0
 

Author Comment

by:BryanEaton
Comment Utility
Apologies for being quiet for a while, I was pulled away from doing this task and had to work on budget allocatons - what fun!

It does work to the point that it will split up the first 2 records, then bomb me out still.

I tried copying the "remarks" field from record 1 into records 2, 3, 4, 5 etc so all were the same length and content, but it still bombs me out after record 2.

If I could eMail you a copy of some of the real data, with the code I have tried, that may help.

Many thanks

Bryan
0
 
LVL 8

Expert Comment

by:Eric Flamm
Comment Utility
How about posting the code here first - it may not be a data issue, and the EE guidelines discourage e-mailing as it disrupts the flow of the solution.

-ef
0
 

Author Comment

by:BryanEaton
Comment Utility
OK -

Here is the code I am using:

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim db As Database
Dim rstImport As Recordset
Dim rstResults As Recordset
Dim strMulti() As String
Dim strLine() As String
Dim i As Integer

Set db = CurrentDb
Set rstImport = db.OpenRecordset("tblImport3")
Set rstResults = db.OpenRecordset("tblResults")

rstImport.MoveFirst
While Not rstImport.EOF
   strMulti = Split(rstImport("Remarks"), Chr(10))
   i = 0
   On Error GoTo NextRecord
    Do
    strLine = Split(strMulti(i), ",")  'It hangs up here with a "subscript out of range" see note below
    rstResults.AddNew
    rstResults("fldDrawingNum") = rstImport("DrawingNum")
    rstResults("fldSheet") = rstImport("SheetNum")
    rstResults("fldADate") = strLine(0)
    rstResults("fldA") = strLine(1)
    rstResults.Update
   i = i + 1         'Get next revision record
   Loop
   Erase strLine()
   Erase strMulti()
NextRecord:
rstImport.MoveNext
Wend
Exit_Command0_Click:
    Exit Sub

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click
   
End Sub

I have tried the erase strline with and without brackets, and in a variety of places in the code, all to no avail.  Is there maybe some way I can count the # of line feeds in the field, then go round the do loop that many times?

Note:  The field for this record contains 8 lines of data.  When it stops, i=8.  So I assume that as it starts at 0, 8 is out of range.  What confuses me is that it seems to process record 1 just fine, it is record 2 that scews up.  I set the Remarks field of record 2, 3, 4 etc to have the same contents as record 1, so it cannot be the data.

Later on in the data, there are anomolies that I have to deal with, like the first charactors not being a date, the date not being seperated by a comma... but I can deal with that, and those anomolies do not occur in the test data.

Many thanks
0
 
LVL 8

Assisted Solution

by:Eric Flamm
Eric Flamm earned 425 total points
Comment Utility
here's a function that will return the number of line feeds in any given string. But I don't think that's the problem.

Public Function CountLF(strText As String) As Integer
    Dim i As Integer
    Dim iChar As Integer
   
    i = 0
    iChar = 0
    While InStr(iChar + 1, strText, Chr(10)) <> 0
        iChar = InStr(iChar + 1, strText, Chr(10))
        i = i + 1
       ' Debug.Print i, iChar
    Wend
    CountLF = i
       
End Function

i think the problem is your do loop - it loops 1 too many times; when it throws the error, it skips the erase statements, so the arrays never get reinitialized. The array knows its upper bound - you can use the UBound function to determine it. So you could just do:

for i=0 to ubound(strmulti)

to loop through exactly all of the strmulti elements.

-ef
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now