Import XLS into Access, but a little more complicated!

[HunterN]

Hi,

I am pretty new to Access! So far I have created the table that will accept my Excel spreadsheet. I have made a Form to view the table once it is populated. Also I have code that works to bring in an Excel sheet. And I can open the form and view the table. But here is my problem.

The Excel sheet has Columns A thru Q (I only show 5), and varying amount of rows. The problem arises because in each Excel sheet the amount of rows that pertain to a specific record is variable. By this I mean that in the Excel spreadsheet, for the first record in ColumnA I could have two rows that go with this record. Then starting in ColumnA row 6 i could have seven rows that go with this record.

NAME	  SIZE	DESCRIPTION	    START 	    STOP 
Pa1	   2	long	                          951	          952
		description		
Pa2 	   2	long 	                          953	          954
		and more		
		stuff		
		to come		
		which 		
		makes it longer		
		than the one above		
PSUP1   2	Long	                          955	           956

So when I pull in the spreadsheet I need a way to read it to put each into one row in Access.

I hope this made sense. If you have any suggestions for me, please help! Thanks,
Nancy

 

[Joe4]

Access works much differently than Excel, as it is a relational database program, and not a spreadsheet program. As such, each record in has ALL the fields of that table associated with it. And records should not be continuations of records previous to it (each record should be a complete entity unto itself).

So you should not have blanks in certain fields if they really should have a value in them. So I think you will need to clean-up your data first (in Excel_ before you import it.

Since you are pretty new to Access, I would highly recommend reading the following article, which describes many important things about relational databases, especially rules for Data Normalization which should guide you in how to design your tables: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

 

[HunterN]

Hi,

Well I was successful in bringing in my Excel spreadsheet into Access. As in my example I have fewer actual RECORDS to go into Access than there are the total number of rows in Excel. I read these in by using vb code to loop through the Excel spreadsheet. Now I have another problem that I am hoping someone can help me with.

As you see in the initial example I have a total of three (3) RECORDS. I have defined the 'Description' field as Memo. So I want the description to have multiple lines in it. How can I display these on multiple lines when they show up on the form?

I have the following code pulling it into a variable that I write to the field.

Private Sub OK_Btn_Click()
    Dim i As Integer

    Do Until start_row = ColAlastRow

    strsql = "INSERT INTO Puffin_db_Table " _
                   & "(VarName, Size, LongDescription, StartPosition, StopPosition)  VALUES "('" & hold_Var_Name & "','" & hold_Var_Size & "','" & hold_Var_LDescp & "','" & hold_Var_StartPos & "','" & hold_Var_StopPos & "')"
        
      DoCmd.RunSQL strsql

     Loop

End Sub


Private Sub MoveDatatoAccess()
    Dim i       As Integer
    Dim myCol   As Integer
    Dim strsql  As String
    
    
    hold_Var_Name = ActiveCell.Value
    hold_Var_Size = Range("B" & start_row).Value
    
    ActiveCell.Offset(0, 2).Activate
    myCol = ActiveCell.Column
    hold_Var_LDescp = ""
    For i = start_row To end_row
        If IsEmpty(Cells(i, myCol)) Then
            i = end_row
        Else
            hold_Var_LDescp = hold_Var_LDescp & Range("C" & i).Value & Chr(13)
            ActiveCell.Offset(1, 0).Activate
        End If
    Next i
    
    Range("D" & start_row).Select
    hold_Var_StartPos = ActiveCell.Value
    
    Range("E" & start_row).Select
    hold_Var_StopPos = ActiveCell.Value
    
    Range("F" & start_row).Select
    hold_Var_Action = ActiveCell.Value

End Sub

The above code is all within a loop so I can capture the rows for each RECORD.
The chr(13) was suppose to represent a LineFeed character. I was hoping that it would write it on the next line, so that when it is displayed in the form it would have multiple lines in the memo field. What happens is I get a little box with a '?' mark inside between the data, and they are all on the same line.

Any help would be much appreciated.

Thanks,
Nancy

 

[boblarson]

you must use Chr(13) & Chr(10) together, not one or the other. Or, since this is in VBA, you can simply use vbCrLf to be the equivalent. But using Chr(13) by itself is not the way it should be used.

 

[HunterN]

Bob,

Thanks so much for correcting me. That worked perfectly!

Nancy