manipulation Excel spreadsheet before import

[mlrucci]

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?

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!

[Micron]

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.

[CJ_London]

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

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

[mlrucci]

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

[Gicu]

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,

[mlrucci]

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.

[Gicu]

You're very welcome, good luck with your project!

Cheers,