I have been tasked with taking an Excel spreadsheet and figuring how to appy all the proper formatting to create the flat file for IC811, IC812, etc...
I've been working with some sample data (only about 10 rows) and cannot seem to get the file to save and retain formatting. I have tried saving the file in different ways hoping to get a nice fixed-length flat file out my Excel spreadsheet.
I tried saving a space-delimited (*.prn) file. I then open the file in Textpad and it appeard to automagically insert CR/LF's and I end up with alot more rows than I should.
I tried saving as a csv, hoping to just remove the commas, but I lose the blank spaces.
So I have two questions:
1) Can someone tell me better way to do this?
2) Has anyone used Addins to load this, and if so, would you please elaborate? My functional compadre believes using Addins for IC conversion is inherently flawed and cannot be done. I believe it can (theoretically). :nix:
TIA,
~Shane
If I read the last question(s) correctly, my answer is "Yes, we do load IC11 and IC12 via add-ins...on a very limited basis." As far as being flawed? Well, certainly not as fast as the IC811 or 812, but I do not believe it is inherently flawed. There are some things you have to work around (IC12.2 - ITEMSRC, as an example).
Personally, the first company that we built in version 8 was done with Add-Ins, simply because I ran out of time to get the IC811 to work. In our case, we created a couple of custom screens that converted a csv file to the flat file (which I believe we "lifted" from version 7).
As far as the conversion of Excel to flatfile. I have a suggestion (one which I have conveniently NOT tested in quite a while). If you are comfortable in Access:
1 - Bring the Excel table into an Access table.
2 - Create a make table query that concatenates each line into a single field.
3 - Export the table as a txt or csv file.
You could probably do the concatenation in Excel, but I am always leary about the xls-to-csv or csv-to-xls conversions.
I'll keep thinking about it and let you know if I come up with anything more concrete.
rw
I'm considering attempting to just export the xls to csv, write a 4gl program to read the csv and spit out a tape file. I don't know alot about 4gl, but I think i can define the workdef for the input csv and output tape file, simply read each line in the csv and do a bunch of moves to the output file. It seems kinda kludgy just to get a fixed-length file out of an xls file, but there doesn't appear to be any built in way to do this directly in Excel.
~Shane
We used IC812 to convert a health system we had affiliated with just over a year ago. We were the larger of the two health systems so we brought Lawson to them. Normally, we use Add-ins to load new items to the IC12 but since we wanted to bring over item cost information from their legacy system we used IC812.
Roguewolf is correct about using Access to create to a proper conversion file. Excel just doesn't do it. I tried for weeks to get it to work until our Lawson consultant provided Access tables that I used to import the data from Excel. The columns in the table were formatted for IC812. I just had to make sure the columns in my spreadsheet matched up with what the Access table was expecting. Then I exported that as a txt file.
But my view is, unless you really need cost info up front and the items are not going on contract(s) and can't wait for the system to update the cost with the first purchase, then just use Add-ins.
Jon