Results 1 to 7 of 7
  1. #1
    mlrucci is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2018
    Posts
    202

    manipulation Excel spreadsheet before import

    Good morning. I have data coming from a different server that I need to import into my access db. The data is in csv form and when converted to xlsx has blank columns and rows that need to be deleted. I am attempting to do a multistep process to:
    1. convert csv file to xlsx. Is this possible? Have been unable to locate information as to how to do this.
    2. Delete blank columns for proper import. Will tackle when I get step 3 completed.
    3. Delete first 21 rows of the worksheet (there is built in code to create other information and picture preventing proper import into access) I either need to delete the rows or start on row 22. Still trying to figure out if I can select the row to start the import. I have starting dabbling in the code, but am having a hard time with access recognizing the worksheet that is open. Do not know why.
    4. Save the new sheet.
    5. Import the data into a preexisting table. This part I have done and is working great on the other data I import.

    Question, is the above possible? If so, any insight into as to resources to accomplish this?
    Below is the code I have been playing with. It opens the workbook, but then I get the error "Please check error 1004 Sorry, we couldn't find LIRxlsx.xlsx. Is it possible it was moved, renamed or deleted?" The spreadsheet is open. Why can it not recognize the workbook?

    Code:
    Option Compare Database
    Option Explicit
    Dim wb              As Excel.Workbook
    Dim xlApp           As Excel.Application
    Dim FilePicker      As FileDialog
    Dim SelectedFile    As String
    Dim vrtSelectedItem As Variant
    
    
    
    Private Sub cmdDeleteRows_Click()Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
    Set xlApp = CreateObject("Excel.Application")
    
    
    On Error GoTo Err_Display
        FilePicker.AllowMultiSelect = True
        FilePicker.Filters.Add "Excel", "*.xls*", 1
        FilePicker.InitialFileName = "C:\Users\"
        FilePicker.Title = "Please Select the Excel Data..."
        FilePicker.Show
        For Each vrtSelectedItem In FilePicker.SelectedItems
        Application.FollowHyperlink vrtSelectedItem
        Next
            'xlApp.Visible = True (if using filepicker, then this is opening another excel worksheet.  Not used here as we are opening a specific file
            'Set wb = xlApp.Workbooks.Open("LIRxlsx.xlsx", True, False):Failed as it does not recognize the workbook
            Set wb = xlApp.Workbooks.Open("LIRxlsx", True, False)
            wb.Sheets(1).Rows(2).Delete
    
    
            'if more than one sheet in wb, then use code below
            'wb.Sheets("Sheet2").Rows(2).Delete
    
    
    Err_Exit:
        Exit Sub
    Err_Display:
        MsgBox "Please check error " & Err.Number & " " & Err.Description
        Resume Err_Exit
    
    
    End Sub

    Any insight to my thought process would be very helpful. I even thought maybe I don't have the right references turned on under tools as I have been playing with this idea for several hours and every time I try an approach, I get errors. Hummmm I could even be looking at this process all wrong.



    Thank you!

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This will probably take some time and you answering a few questions. To answer generally, all should be doable based on how I interpret your post. IMO, approach would be to import the file as a sheet (ribbon: Data > Get Data > From File...), edit (clean up, remove rows, blank columns, etc.) and link that sheet in Access. Use the linked sheet to create a properly normalized table with the right data types. Therein lies Q1: sheet name and workbook name should never change, otherwise relinking in Access will be necessary every time and the table creation part would need to be modified, so what is the process like on the Excel side?
    - New file and/or sheet every time, or does the original data just get wiped out?
    - Are the deleted columns/rows always going to be the same ones?

    Re: the error - AFAIK, you need the complete path and file name to open; you can't just pass the file name to the Open method

    Your code could use some cleanup but for me, why open the dialog using multi select when your post seems to be only about one sheet?
    Finally (for today) is there some reason why you can't skip Excel altogether and just import csv data into a staging table (similar to linked sheet that is used to populate the final table as mentioned above but the staging table would be an Access table created from the csv data).

    Some sample data might help others to assist you; even better if you show the desired outcome. If not as attached files, then copying/pasting from Excel will automatically create a table in your post. Pics of data are of little use - no one can access the data to perform any trials and most won't type all that out manually.

    EDIT - one pic that is often of value is that of your relationships window.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Would need to see an example of your .csv to confirm but you should be able to do this with one query importing the csv and ignoring rows and columns as required. Generally better not to convert to .xlsx first as it can mess with data formats.

    Your query might look something like this

    Code:
    INSERT INTO DestTable ( field1, field2, field3 )
    SELECT F1, F2, F6
    FROM (SELECT * FROM [TEXT;DATABASE=C:\path;HDR=No].import.csv)  AS txt
    WHERE F2<>""
    this is importing columns 1,2 and 6 and ignoring rows where F2 is blank

    Note Hdr=No - this is because almost certainly the first 21 rows may have text in the first column and nothing in the second - with 21 rows, sounds like a SAP export and this is the technique I use.

    To get it to work for you needs 3 steps:

    1. start with a basic - SELECT * FROM (SELECT * FROM [TEXT;DATABASE=C:\path;HDR=No].import.csv) AS txt

    2. next apply criteria to exclude the rows you don't want (might involve more than one column or different form of criteria, depending on your actually csv data)

    3. finally convert to an append query and assign the values that you want for the destination table

  4. #4
    mlrucci is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2018
    Posts
    202
    Thank you very much for your responses. To make a long story short and after discussion with my team, they want to use xlsx as other teams use this in xlsx format. In retrospect and in discussions, they can load the data in xlsx in the backend. So from here, I need to then take that data in xlsx format and clean it up. I would like to automate the process. Because the data is stored in different folders and different drives; I need to use the file picker so that they choose the correct workbook. I have no problem selecting the file and opening the correct workbook. What I need to do from here is to automatically clean it up and import the data. Since working with the group and discussing my challenges, they have provided me with another workbook with the same data. The only thing I need to do from here is delete the first 3 rows of the xlsx workbook or start from the 5th row. This was a game changed. Here are some of the questions asked from Micron.

    Q1: sheet name and workbook name should never change, otherwise relinking in Access will be necessary every time and the table creation part would need to be modified, so what is the process like on the Excel side? The sheet name and never changes. The workbook name will change as it is based on a backend of anther program and is date based. Hence using the file picker. I have created a table I would like to import the table into with the correct fields and formats.

    Q2:
    New file and/or sheet every time, or does the original data just get wiped out? The information is laid out and formatted the same every time. It's the data that changes.

    Q3: Are the deleted columns/rows always going to be the same ones? Yes

    I agree the code needs to be cleaned up. I am just trying to get it started. I am a novice to vba coding and it takes me a while. Self taught. I can remove the multiselect, I just had that code from another code I am using (CopyPaste). I am going to attach a very stripped down version of what I am trying to accomplish. In the end, just need to develop a way to either delete the first 3 rows as the headers are starting on the 4th row and the data starts on the 5th. Any further assistance would be greatly appreciated


    TestDB_Conversion.accdbLIRxlsx.zip


  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please have a look at the attached updated file. The TransferSpreadsheet has a range argument that you can use to import the table into Access.

    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    mlrucci is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2018
    Posts
    202
    Gicu, thank you very much! It worked great! I wound up fixing all my issues with the above and then did a bit of adjustment with my needs. I imported the whole worksheet into a temp file and then appended what I wanted into my table. After completed, I then deleted my table. I never gave it a thought that I could start at A4 either. This put me on the right path and it surely helped. You are the bomb! Again, thank you.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're very welcome, good luck with your project!

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 07-10-2019, 07:34 PM
  2. Import Excel spreadsheet failing
    By swas in forum Import/Export Data
    Replies: 3
    Last Post: 03-25-2019, 11:43 PM
  3. excel conversion/import/manipulation
    By Jen0dorf in forum Access
    Replies: 4
    Last Post: 03-22-2016, 01:41 PM
  4. Import From Excel Spreadsheet on Sharepoint Site.
    By Robeen in forum Import/Export Data
    Replies: 1
    Last Post: 01-11-2016, 09:55 AM
  5. import excel spreadsheet though outlook
    By bopsgtir in forum Import/Export Data
    Replies: 0
    Last Post: 03-18-2011, 09:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums