Help Importing Excel File (1 Viewer)

tmyers

Well-known member
Local time
Today, 09:49
Joined
Sep 8, 2020
Messages
1,090
I have an Excel file that is formatted in a way that is giving me a fit trying to import it. It has a bunch of merged cells that I need to unmerge but the main problem I am having is there is data below lines that I need to keep tied to the line above it (also out of curiosity, why are we unable to attach Excel files?)

1652890979738.png


Looking at the above picture, you can see that the description is below the catalog number and shifted over a column. How, when I import, can I move that up to the same row as the catalog number? For these particular files, the description is very important to keep due to being government jobs (in this instance, roadway lighting)

The other hurdle is for the catalog number, D-N is merged and for the description, E-N is merged but I assume I could solve that with a simple:
Code:
Range("D:N").Unmerge

Could someone give a little guidance on how to handle this?

Edit:
I know this will likely be done via looping and finding rows within column E that contain text, then moving them up to be in sync with the catalog but want to make sure that is correct method to use.
 

Attachments

  • Example.zip
    11.2 KB · Views: 177
Last edited:

plog

Banishment Pending
Local time
Today, 08:49
Joined
May 11, 2011
Messages
11,611
1. One time import or will this be an ongoing thing? Once a week/month?

2. Can you provide a sample file?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:49
Joined
Feb 28, 2001
Messages
26,999
In Excel, what you appear to have shown us might not matter regarding merging or unmerging. It is the behavior of Excel that if you load up a string that is longer than the apparent width of the column BUT there is nothing stored in cells to the right of the column, you see the whole string anyway. Which means that your strings that start in columns D or E are probably complete within those cells. That is, more specifically, that on the lines in bold, column D will have all of that text and column E will be empty. In lines where column E has something, column F will be empty. More specifically, in order to show what you have shown there, the "empty" columns cannot even contain a blank. I specifically mean EMPTY.

Looks to me like you could import this using an Excel Application Object. Or you could just import to a pre-defined table of all text and see where things "landed." You appear to only have five or six possible landing zones. On the lines with something in column A, 5 zones. On the lines with something in column E but not in columns A-D, that is your sixth landing zone. And things that are blank in A-E will probably be blank the rest of the way.

You would then immediately use a recordset on the imported table. However, DO NOT do anything else to the table that could update it as that would destroy the order of the records. After an import to an unsorted table with no keys, record order will be the same as the order in which the records were imported. After ANY attempt to update, however, that order CANNOT be guaranteed.

Your recordset operation would then consist of a loop that checks whatever was in field A and field E to decide which of the two kinds of lines you have at the moment. If the line is ALL blank, you have nothing and can skip it. Based on what I see, you might have a second recordset open for writing the imported results to a regular table, but you would defer writing anything that had something in column A until you have found the line that follows it that had something in column E.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:49
Joined
Sep 21, 2011
Messages
14,041
Move the bottom line of each to a column to the right?
I'd tidy up this sheet in Excel before importing if possible.
 

tmyers

Well-known member
Local time
Today, 09:49
Joined
Sep 8, 2020
Messages
1,090
Edit:
To Plog:
1. This is from a vendor we don't use a ton, but frequent enough that it has become a requested addition to my DB's import function.

2. I tried to attach the file and it said it was not supported (its just an .xls)

To Minty:
That is what I prefer to do. I like to tidy up all my Excel files via VBA before importing to ensure everything is where it needs to be and data gets verified etc.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:49
Joined
Sep 21, 2011
Messages
14,041
Zip it :( or just change to suffix to one that is alllowed and mention it in the post.
 

tmyers

Well-known member
Local time
Today, 09:49
Joined
Sep 8, 2020
Messages
1,090
I always forget about compressing the file. Thanks Gas! The original post now includes the file.
 

plog

Banishment Pending
Local time
Today, 08:49
Joined
May 11, 2011
Messages
11,611
The merged cells really aren't an issue, its the non-standardness of it all.

1. Varying rows in a record. The first record (Type=0001/Qty=38) takes up 3 lines (2-4), The 10th record (Type=0006/Qty=2297) takes up 7 rows (29-35)

2. No good record start/end delimiter. A newline with a character in the first column seems to be a good marker, until you scroll to line 86 and see that a new record doesn't need a Type value.

I think the way to do this is to write your own parsing script--perhaps python or R. You read in the file line by line and build a table with the data you want. At the end you spit that data out into a text file that you then import into Access.

Or, you might be able to do it in Excel. Create a tab called 'input' and one called 'output'. The output tab would have the headers of the fields you ultimately need out of your file. You paste the data you get into 'input', then build a VBA function that goes line by line and extracts the data and puts it into the 'output' tab.
 

tmyers

Well-known member
Local time
Today, 09:49
Joined
Sep 8, 2020
Messages
1,090
I may just try to import it as is and see what nonsense occurs and if I can work it from there within Access rather than Excel.
 

tmyers

Well-known member
Local time
Today, 09:49
Joined
Sep 8, 2020
Messages
1,090
Looking over it, I think I could handle this with simply deleting the first two cells in the description column and shift the column up. That would bring the descriptions to the same row as the catalog number then it makes it easier.

That is all in hopes that the 2 row gap is always the case, otherwise it would break. I could also potentially do a loop within a loop.
 

plog

Banishment Pending
Local time
Today, 08:49
Joined
May 11, 2011
Messages
11,611
That is all in hopes that the 2 row gap is always the case, otherwise it would break.

Sounds like a plan. I can see I am, nor never was, needed.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:49
Joined
Feb 19, 2013
Messages
16,553
you can do it with one query with the help of 3 vba functions

Put these 3 functions in a standard module

Code:
Function getPreviousCat(v As Variant) As String
Static p As Variant

    If Nz(v, "") <> "" Then p = v
    getPreviousCat = p

End Function

Function getPreviousType(v As Variant) As String
Static p As Variant

    If Nz(v, "") <> "" Then p = v
    getPreviousType = p

End Function

Function getPreviousQty(v As Variant) As String
Static p As Variant

    If Nz(v, "") <> "" Then p = v
    getPreviousQty = p

End Function

And this is the query

Code:
SELECT *
FROM
(SELECT getprevioustype([Type] & "") AS Typ, getpreviousqty([Qty ]) AS Qt, getpreviousCat([Catalog #]) AS [Catalog], XL.F5, XL.[Unit $], XL.[Ext $]
    FROM
    (SELECT * FROM [Quote 2009-22-16169-3$] AS xlData IN 'D:\Dev\1_temp\example.xls'[Excel 8.0;HDR=yes;IMEX=1;ACCDB=Yes])  AS XL
WHERE (((XL.[Qty ]) Is Not Null)) OR (((XL.F5) Is Not Null)))  AS r
WHERE (((r.F5)<>"")) OR (((IsNumeric([Typ]))=False));

copy and paste the above into the sql window and change the path to wherever you have your example.xls file

this part is the equivalent of your linked table - path to be change in red
(SELECT * FROM [Quote 2009-22-16169-3$] AS xlData IN 'D:\Dev\1_temp\example.xls'[Excel 8.0;HDR=yes;IMEX=1;ACCDB=Yes]) AS XL

this part - removes blank rows and populates the blank fields on the row that contains a value in F5 (which can be z zls), utilising the 3 functions
(SELECT getprevioustype([Type] & "") AS Typ, getpreviousqty([Qty ]) AS Qt, getpreviousCat([Catalog #]) AS [Catalog], XL.F5, XL.[Unit $], XL.[Ext $]
FROM
....
...
WHERE (((XL.[Qty ]) Is Not Null)) OR (((XL.F5) Is Not Null))) AS r

and the final outer part removes the rows that are not required
SELECT *
FROM
....
....
WHERE (((r.F5)<>"")) OR (((IsNumeric([Typ]))=False));

and this is the result - you can change fieldnames as required


TypQtCatalogF5Unit $Ext $
1051RK8 MI-2588-C-KITHMAO LED IV, P4 performance package, 3000K, 347/480 volts, Housing, gray, Medium asymmetric wide
1051RK8 MI-2588-C-KITHMAO LED IV, P4 performance package, 3000K, 347/480 volts, Housing, gray, Area wide
1051RK8 MI-2588-C-KITHMAO LED IV, P3 performance package, 3000K, 347/480 volts, Housing, gray, Medium asymmetric wide
1051RK8 MI-2588-C-KITHMAO LED IV, P3 performance package, 3000K, 347/480 volts, Housing, gray, Medium asymmetric wide
1051RK8 MI-2588-C-KITHMAO LED IV, P3 performance package, 3000K, 347/480 volts, Housing, gray, Area wide
1051RK8 MI-2588-C-KITHMAO LED IV, P3 performance package, 3000K, 347/480 volts, Housing, gray, Medium asymmetric wide
1051RK8 MI-2588-C-KITHMAO LED IV, P3 performance package, 3000K, 347/480 volts, Housing, gray, Medium asymmetric wide
1051RK8 MI-2588-C-KITHMAO LED IV, P4 performance package, 3000K, 347/480 volts, Housing, gray, Medium asymmetric wide
1051RK8 MI-2588-C-KITHMAO LED IV, P3 performance package, 3000K, 347/480 volts, Housing, gray, Area wide
1051RK8 MI-2588-C-KITHMAO LED IV, P4 performance package, 3000K, 347/480 volts, Housing, gray, Area wide
1051RK8 MI-2588-C-KITHMAO LED IV, P1 performance package, 3000K, 347/480 volts, Housing, gray, Long and narrow
1051RK8 MI-2588-C-KITHMAO LED IV, P2 performance package, 3000K, 347/480 volts, Housing, gray, Area wide
1051RK8 MI-2588-C-KITHMAO LED IV, P1 performance package, 3000K, 347/480 volts, Housing, gray, Long and narrow
1051RK8 MI-2588-C-KITHMAO LED IV, P2 performance package, 3000K, 347/480 volts, Housing, gray, Long and narrow
1051RK8 MI-2588-C-KITHMAO LED IV, P1 performance package, 3000K, 347/480 volts, Housing, gray, Long and narrow
1051RK8 MI-2588-C-KITHMAO LED IV, P2 performance package, 3000K, 347/480 volts, Housing, gray, Long and narrow
1051RK8 MI-2588-C-KITHMAO LED IV, P2 performance package, 3000K, 347/480 volts, Housing, gray, Long and narrow
1051RK8 MI-2588-C-KITHMAO LED IV, P2 performance package, 3000K, 347/480 volts, Housing, gray, Area wide
1051RK8 MI-2588-C-KITHMAO LED IV, P3 performance package, 3000K, 347/480 volts, Housing, gray, Area wide
0013 LUMINAIRE COUNT85RK8 LDV LD CNTRWT MINUS ARM
0013 LUMINAIRE COUNT85RK8 LDV LD CNTRWT MINUS ARM
0013 J BOX COUNTERWE1RK8 MI-2588-C-KIT
0013 J BOX COUNTERWE1RK8 MI-2588-C-KIT
101 PLAN A102LCKIT-120-SS-OS
101 PLAN A102LCKIT-120-SS-OS120FT Stainless Steel Hoist Cable Set with Hardware ( (need to know when tower was installed. and will adjust pricing for other pole heights)
101 PLAN A102WC-1-4X120-SS
101 PLAN A102WC-1-4X120-SS1/4IN 120FT Stainless Steel Winch Cable
101 PLAN B5LCKIT-120-SS-OS
101 PLAN B5LCKIT-120-SS-OS120FT Stainless Steel Hoist Cable Set with Hardware ( (need to know when tower was installed. and will adjust pricing for other pole heights)
101 PLAN B5WC-1-4X120-SS
101 PLAN B5WC-1-4X120-SS1/4IN 120FT Stainless Steel Winch Cable
101 PLAN B5WC-1-4X120-SSField Service Man Day, Zone 1, 1 day (8 hrs) need 8 to cover the 60 hrs.
101 PLAN B5WC-1-4X120-SSPortable drive motor, One circuit, 60hz, 480V, Single Phase, 30 amps
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:49
Joined
May 7, 2009
Messages
19,169
this is VBA alternative.
run form1 and put the path+filename of your excel file.
use the Browse button to locate your file.
click Process button when ready.
 

Attachments

  • QuoteImport.accdb
    552 KB · Views: 149

tmyers

Well-known member
Local time
Today, 09:49
Joined
Sep 8, 2020
Messages
1,090
Sounds like a plan. I can see I am, nor never was, needed.
Not true! Any advice given is always appreciated! :)

CJ, that is quite extensive but I like it!
Arnelgp, I also like the method you did as well. I would have to add a little bit to it, as the file I provided had already been modified to get it to that state (delete header, footer, pictures etc.)
 
Last edited:

tmyers

Well-known member
Local time
Today, 09:49
Joined
Sep 8, 2020
Messages
1,090
Here is more or less what the file looks like in its original format. I already have some code that deletes out all the unneeded information and other items to clean it up.
 

Attachments

  • HOLOPHANE DN WORKBOOK.zip
    43.1 KB · Views: 164

CJ_London

Super Moderator
Staff member
Local time
Today, 13:49
Joined
Feb 19, 2013
Messages
16,553
ignore my suggestion, doesn't cope with all the options such as missing type. I haven't looked at Arnels solution but probably go that way
 

Users who are viewing this thread

Top Bottom