Sage 100

v2014 Std - Trying to import from an *.XLSX (Excel Version 12) file - Physical Inventory counts with VI. Spreadsheet's Row 1 & Row 2 are header rows, data starts in row three. If I delete header rows, import works. Will VI's Skip Record or Select Record function allow me to keep the headers in the spreadsheet and still import the data? Which one should I use? Any tricks to make it work?.

Yes, the Skip record should work if you have it explicitly look for the field names (or a subset of the first X characters).

Wow, two Skip record responses today! @JeffSchwenk https://90minds-com.socialcast.com/stream/live#

The XLSX import functionality actually works now. Can't tell you how long I have been using CSV or TXT imports. @MichaelMcDonald - Is this truly dependable or filled with quirks that you have to navigate around? Are you saying that the entire import fails if you don't skip the header record rows? I would have thought those two LINES will fail and all others (with valid item numbers) would import.

@SteveIwanowski - I'm not sure I understand what you mean?? The 1st Row has column headings --- Cell A1= ""ABC Company"", B1=""Bin"",C1=""Warehouse"",D1=""Item"", etc. 2nd Row has column headings -- Cell A2=""Item Code"",B2=""Location"",C2=""ID"",D2=""Description"", etc. How to set the parameter in the Skip Record to ignore row 1 and row 2?

On the Record tab in the vi job, you can test for the first or many first characters in the first (or other field) to skip.

I would set the Skip to look for Value = ""ABC Company"" in Column 1, Length = 11. And then for Value = ""Item Code"" in Column 1, Length = 9

I would use the ""record"" tab then skip record. see attached.

@JeffSchwenk - The whole VI import fails with an Error 42 - if I try to read the heading rows. If I delete those two rows from my .XLSX sheet then the VI import works. BUT the client wants to keep the headings in the count spreadsheet for future readability.

That's because the header field that is the column for Qty is a string in the headers. If you skip those records using the stuff above you should be fine.

Attached is sample *.XLSX count sheet, my adjusted Skip Records and subsequent Error 42 message. Any Idea's TIA

Put a number in column 5 of both header lines and see if it is indeed skipping them

Why is it looking for a csv file?

Could it be that Item code is not left justified?

Why don't you try removing one row of the headers at a time to see if one of the skip's is working or not working.

I think I would add a column at the beginning, one character wide, and flag the first two rows with an ""S"" in that column. Then the skip record is easily configured in VI.

Tried all of the above suggestions. Thank you! But the only way I can get the import to work is if I remove BOTH row 1 (Column Headings) AND row 2 (Column Headings). So sometimes the client/customer doesn't always get what they want. They should be happy they don't have to hand enter the count sheets into SAGE 100 Physical Count line by line. Thanks Again.

I see you created two skip records, but not any Standard record definition. I have always had to create a column 1 <> blanks kind of definition for the Standard to make things work... be interested to know if it works for you

Be sure to create the Standard after the two skips....

Holy Molely @RandyMarion - That was the trick!! I put the Skip Records before the Standard Record and the import WORKED!! THANKS!!!