Sage 100

When processing imports through VI. I always take the easy way and use CSV. 9PM is not the time to start something new but I am trying to use XLSX with Sage 100 ERP Version 2014. How do you designate the tab or table? Sorry if this is obvious. I could not seem to find an answer.

No clue. If it is like Access, you select the ODBC source and follow the prompts..... See the screen shots. I just tried both Excel and Access and I received error messages both time... Personally, even at 8 AM, it is not a good time to use a non CSV or tab delimited import.

In the past, Sage always recommended using .csv. We had users use .xls in some cases. It seemed the jobs would work for a period of time, sometimes years, and then all of a sudden not work with nothing changing. The workaround was always to redesign it using .csv instead. That always did the trick so I always use and recommend that. Maybe .xls or .xlsx works better with the newer versions of both Excel and Sage, but as long as .csv works, that's my go to.

I might be totally wrong on this but I've always assumed it only used the first tab. Sent from my Windows Phone

Never found a good enough reason not to use csv. Especially since it's always been such a hassle to get an excel file to work!

Sorry. I had a temporary fit of optimism.

It happens every now and then. Tomorrow is another day.

I have only been successful with csv file or possible Excel 4 format which was only one worksheet.

I try and avoid csv. I am paranoid about potential misplaced commas that could unknowingly lead to bad data as extra columns get parsed. I try and use tab delimited or pipe delimited. See tab delimited configuration below.

Agreed. When the clients integrated solution allows customizations I prefer the pipe delimited as well. It is easy to read as well. The project I am working on now, I just had to strip all of the cell embedded carriage returns.

I have been having reliable results with xlsx formats. Never had reliable results with xls. I did not think you could import from other than tab 1.

Wow what a mix. As I have never had success using the xlsx. I have issues constantly using this format even when converting to csv. Just yesterday one column wouldn't read until I took the xlsx file into my old XP, converted it there to xls and then to csv. I did this religiously up until recently but am thinking after yesterday's battle over the one column (numeric-vendor lead time) maybe I just need to make the process a Best Business Practice until versions of Excel are compatible. I suspect it's different versions of excel creating so many variations of failing imports. In any case Get'r Done. Happy Friday, from California - home of the movers and shakers, lol. ( real estate changing hands here in California today with extra dirt/mud. )

I have imported a few xlsx files and I haven't noticed any issues. I haven't thought about it until today but I agree with @RonGouveia and @alanniergarth that it appears to be only reading the first Sheet.

Yup, So the client is not allowed to modify the spreadsheet in any way, so I am writing a macro to strip the carriage returns out of the individual cells, save as a CSV, warn them to close the file (I normally just save back to the Excel format), and ignite the VI Job.