Importing data from Excel to Access 2007 but some data carries to the next lower cell

[Hat2boots]

I am trying to import data from a linked Excel file, but when it was exported from the Supplier in CSV/XLSX format any data in the Description column that was longer than 35 characters it would be inserted to the next cell below showing up as a separate record in the Access Table.

How do I Join them together the description back together?

[zmbd]

+ Is the field length setting for the [Description] field in the access table large enough to hold the extra data?

+ As this is a CSV file, it could be possible to open the file for I/O and parse thru it first using VBA removing the return at the end of the prior line.... Without the data, it's hard to provide more than pseudo code/logic

+ Would you be so kind as to enter the example data (just four rows should do :) ) as a table within a post? Use the [CODE/] format to hold the formatting and the <Space>s instead of <Tab>s for column formatting.
++ Many of our experts (I am one such) are not able to d/l unrequested attachments due to various IT-Security policies and/or firewall restrictions.

+ Information on the table design would also be most helpful.
[TableName]
[TableName]![FieldName] datatype (ie Autonumber, Text(25), etc) PK=Primary Key, FK = Foreign Key

-z

[Hat2boots]

The Raw Data is in Excel. It is already separated. I don't have code to rejoin the Description.

I was thinking something like NextRecord(if(isNull([Column1]),([Column2] & NextRecord [Column2]).

Received data to import:
Column1---Column2
1881074---ZURN QQPC44X 3/4 BARB PEX POLY CPLG
3055338---SPECPROD P-1066 PIPE SUPPORT
----------BRACKET 2IN X 20IN W/EXTRA 1/2IN
----------HOLE 1/2INON 1IN CENTERS 3/4IN ON
----------4IN CENTERS

Data needs to be like after import:
Column1---Column2
1881074---ZURN QQPC44X 3/4 BARB PEX POLY CPLG
3055338---SPECPROD P-1066 PIPE SUPPORT BRACKET 2IN X 20IN W/EXTRA 1/2IN HOLE 1/2INON 1IN CENTERS 3/4IN ON 4IN CENTERS

[Hat2boots]

1637710    zurn q4ps20x 3/4x20 straight length
    pex hot/cold wht tube
1881074    zurn qqpc44x 3/4 barb pex poly cplg
3055338    specprod p-1066 pipe support
    bracket 2in x 20in w/extra 1/2in
    hole 1/2inon 1in centers 3/4in on
    4in centers
 

[jforbes]

If I were doing this, I would stitch the Description back together when Importing the Spreadsheet instead of attempting to fix it later. It's usually easier that way.

I took something that I had that was sort of similar and tweaked it to work with your data. Here is the result:

Private Sub Command0_Click()
    On Error GoTo ErrorOut
 
    Dim sSpreadsheet As String
    Dim oExcel As Excel.Application
    Dim oWorkbook As Excel.Workbook
    Dim oSheets As Excel.Sheets
    Dim oSheet As Excel.Worksheet
 
    Dim lCount As Long
    Dim sSQL As String
    Dim sColumn1 As String
    Dim sColumn2 As String
    Dim sDescription As String
 
    ' Open the Spreadsheet
    sSpreadsheet = "C:\Users\Desktop\Bytes\SampleData.xlsx"
    Set oExcel = New Excel.Application
    Set oWorkbook = oExcel.Workbooks.Open(sSpreadsheet, ReadOnly:=True)
    Set oSheets = oWorkbook.Sheets
    Set oSheet = oWorkbook.Sheets(1)
    For lCount = 1 To oSheet.Rows.Count
 
        ' get Columns
        sColumn1 = oSheet.Cells(lCount, 1).Value
        sColumn2 = oSheet.Cells(lCount, 2).Value
 
        ' Test for New Row
        If Len(sColumn1) > 0 Then
 
            ' Insert Previous Row, if there is one
            If Len(sSQL) > 0 Then
                sSQL = sSQL & sDescription & "')"
                CurrentDb.Execute (sSQL)
            End If
 
            ' Build up new Row SQL
            sSQL = "INSERT INTO ExcelImport ( Column1, Column2) VALUES ( '" & sColumn1 & "', '"
            sDescription = sColumn2
        Else
 
            ' Row might Continue, Add additional Description
            sDescription = sDescription & " " & sColumn2
 
            ' Test for completely blank Row and end of Spreadsheet
            If Len(sColumn2) = 0 Then
                If Len(sSQL) > 0 Then
                    ' Insert Last Row
                    sSQL = sSQL & sDescription & "')"
                    CurrentDb.Execute (sSQL)
                End If
 
                'Exit out of Looping
                Exit For
            End If
        End If
 
    Next lCount
 
    MsgBox "Import complete.  " & lCount & " rows processed."
 
ExitOut:
    'Clean up and release the Spreadsheet if it is opened
    If Not oWorkbook Is Nothing Then
        oWorkbook.Close SaveChanges:=False
        Set oWorkbook = Nothing
    End If
    If Not oExcel Is Nothing Then
        oExcel.Quit
        Set oExcel = Nothing
    End If
    Exit Sub
 
ErrorOut:
    MsgBox (Err.Description)
    Resume ExitOut
End Sub

You'll need to play around with it and make it yours, but I think that should get you going in the right direction. You'll also need to add a reference to the Excel object library in the Visual Basic IDE.

[Hat2boots]

jforbes reading your code I believe that you have the solution to my problem. New problem is that I am not that proficient in SQL/VBA. Is there a MS Access solution?

Thanks for the code, I will try to stumble though VBA to see if I can get the code to work.

[NeoPa]

Hat2Boots:
New problem is that I am not that proficient in SQL/VBA. Is there a MS Access solution?

They're all part of Access to be fair, but certainly SQL, and then VBA, are increasingly complex areas of the whole product. It's usual to start with the basics then move on to working with the SQL directly and then, later still, to get more into the VBA side of things.

Unfortunately for you, in this particular instance, what you need requires VBA and SQL. I see no way to accomplish it without.

[jforbes]

I agree with NeoPa, there is not a native point and click solution for your situation that I can think of. One way or another it will involve either SQL, VBA, both SQL and VBA, or manually correcting the data.

...Searching the Internet, there seem to be some third party solutions that might work in Excel to merge the rows before they are imported, but I've not tried any. If you would rather not take the plunge into developing in Access, this may be a solution for you, but this sort of thing usually comes with a dollar value attached to it.