Problems with an Excel-CSV-File

Hi all I have a problem with an Excel-Sheet: Our Customer is using an Excel-Template to insert “work-times” while the employees are out (at other customers). We want now to import the lines of that excel-sheet into Navision. We save the Sheet in CSV-Format (Comma-Separated). Our problem: Because of a Header (visible part with logo and text in the sheet) excel exports them also into the csv-file. That makes it impossible to import the the csv-file into navision. Any suggestions? Thanks for help! Ben Benjamin Crause webmaster@amargosasun.de Germany

Hi Ben! Maybe it could help, if you put the line CurrFile.SEEK() in the OnPreDataport-Trigger ( is where the “real” data begins), to skip the header-lines!? Bye, Joerg Joerg A. Stryk Apollo-Optik, IT/ERP

Thanks. Mmmhhh…myabe youre right. But: CurrFile.SEEK(Position) <-Position is an Integer. How the Hell should I know at which position the real data begins? How do I use the Position (Integer-Parameter) do set my position in a file. What is position referring to? Line? Byte? No. of Char? Admin: By the way: Why is a file of CSV/XLS not allowed??? Bye Benjamin Crause webmaster@amargosasun.de Germany

quote:


Originally posted by Amargosa: Admin: By the way: Why is a file of CSV/XLS not allowed???


Only zipped files less than 256KB are allowed, simply to save space. Best regards, Erik P. Ernst, webmaster Navision Online User Group

Hi Ben! The Position is given in bytes. Isn’t the Header-Part of the CSV-file constant? Then you could open the CSV in a simple Text-Editor (one which shows the cursor-position e.g. EditPad) and just count it out, once! Or if just count the number of header-lines and do OnPreDataport() FOR i := 1 TO DO CurrFile.READ(TextVariable); (This requires TEXTMODE to be TRUE) If the Header-Part is variable, maybe you could print some special characters after the header e.g. ###, then you could do this: OnPreDataport() WHILE ReadText <> ‘###’ DO CurrFile.READ(ReadText); I haven’t tested these options, but i can imagine that they’ll work … Bye, Joerg Joerg A. Stryk Apollo-Optik, IT/ERP

Thanks for ya Help! I will try it at Monday. Thx Ben Benjamin Crause webmaster@amargosasun.de Germany

I wouldn’t use a CSV file at all! Instead I would directly read data out of the Excel Worksheet usind Automation: Function XLSGetValueDecimal(Col, Row : Integer) : Value : Decimal; Begin xlRange := xlWorksheet.Range(makeCell(Col,Row)); st := xlRange.Text; IF NOT EVALUATE(Value,st) THEN Value := 0; End; CSV files are tricky if you have Text-Values inserted. Imagine, you have a Text which contains a ’ or a ; → Your Dataport will crash and it’s extremely time consuming to find the line which caused the error. Marcus Fabian phone: +41 79 4397872 m.fabian@thenet.ch

Daatports are bad news: as Marcus said, it is better to use an Automation object in cases like this. (Although this is tricky, because you have to have Excel installed properly on all Navision workstations for the Automation to work. If you have random versions of Excel on the workstations, or if you have pirated copies of Excel, you can run into problems.) CSV’s are bad news: you get ugly results when you have commas and/or quotes in your data. If you must use a text export from Excel, use a tab-delimited format. Excel has two powerful programming environments of its own: the old macro language, and a special version of Visual Basic. If you really really want to use a text export to take data out of Excel, you should imbed a macro in the spreadsheet which exports the data from a specified range (hopefully a NAMED range) instead of simply doing a save-as on the entire spreadsheet. And once you have the text file, you should consider using a nonprinting report instead of a dataport to pull the text file’s data into Navision. It sounds this spreadsheet was cobbled together by someone at your customer’s accounting office. That means it does NOT have named ranges— but hopefully you can give it some named ranges. (I have been given MANY spreadsheets over the years with data which needs to be imported into Navision or some other package— and never ONCE have the ranges been named. But, it is easy to name ranges.) I might question whether Excel is the appropriate front end for this payroll process anyway. This sounds, actually, like something the client could track through the Time Journal. Or, failing that, they might want to build a simple database in FoxPro, Access or even Microsoft Works. (Excel is a great product, but it is often used for inappropriate purposes.) ------- Tim Horrigan

My advise here is to add a macro to the template (assuming the customer wants to keep working with Excel). That macro can do a lot of pre-send checks (required fields filled? No “impossible” data? etc.) and makes sure the resulting file is exactly formatted as you need for import. I’ve done such a macro for a customer where regional offices (throughout the world) are sending lists of invoices to be made by the central invoicing office (for charging royalties, shared costs, license fees, etc.). By using this template all files are properly formatted CSV’s and are automatically send to the correct location on the server of the central office. A clear button on the template (“Do Upload”) makes it easy for the users to use the right way of sending. John