BAQ export to .csv formatting issues

So, I’ve searched all over and can’t find what I’m trying to do – and I realize I’m probably not searching correctly…but here I am…

I’ve created a BAQ to export Job information to be used to create some custom reports for production in an Access DB. I was SO proud of myself for creating the BAQ and getting it to export. Then we realized we had an issue with the formatting. Our internal part numbers are in xxx.xxxxxx format – so I see my problem is that . in the middle of that – when it gets to a .csv file opened in Excel it will drop and trailing 0’s - which we need to keep because it’s part of the part number.

The other issue is the Date column. It’s formatted just as mm/dd/yyyy in the BAQ but comes up in the .csv file as date/time and the time is causing problems.


Hi Wendy! How are you generating the Excel file? Did you create an SSRS report, or are you copying to excel from the BAQ results?

I exported the BAQ using the BAQ Export Process. My only option on the file types to export are XML and CSV. I’ve not gotten very far on the SSRS report creation side of life.

Maybe I’m taking the long way around all this anyway. My ultimate goal is for the data in the BAQ to update a file on an automated schedule – if there is a way to do that using an Excel file that’s great too.

I haven’t used the BAQ Export Process before, but one idea you could try:

Create calculated fields in the BAQ for the part number and date in nvarchar format. For the date, use the convert to string function, for example: convert(varchar, Project.StartDate, 101)

I wonder if converting them to strings in the BAQ would keep the formatting once it gets to CSV.

Got my interest sparked to try the BAQ Export Process. That method worked for me for the date field! :slight_smile:

Ok - I will give it a try… have to admit I’m a novice with this stuff and I’m definitely figuring it out as I go …

Well…I’m happy to report that we were able to change the formatting on the output and save it and now it seems that when we overwrite it with the latest and greatest export from Epicor the formatting stays! I can rest my brain on this one and move on to other things that are tormenting me!

Thanks for the help! Even though we worked it out I’m going to test my skills and try doing it with a calculated field

1 Like

First off, don’t assume that what Excel does to a CSV is what Access would do to it. Excel does lots of “favors”.

Nearly any method of trying to make a string of numbers stay a string of numbers, and not be converted to a number, is almost futile in Excel.

If you’re manually typing a string of numbers into Excel, you can start the cell with a single apostrophe ' like:


When I hit enter it will retain the fact that it is text and doesn’t lose that last character.

And the Length of that cell is 7. Not the 8 that it would be if you include the leading '

However, adding that to the CSV data like in lines 4 or 5 below:




While they did remain as text, they now have an extra character. And the length of either of those two yields 8

Have you reviewed the output if exported as an XML format? Although harder initially to learn to use, in the long run, it might be faster.

We weren’t able to link to the XML automatically with the Access DB. Hopefully, at some point we’ll be able to generate all of the reporting that we need directly from Epicor and not have to rely on Access – but for now it’s a lifesaver while we’re in the learning process.

Have you thought about having Access query the SQL DB directly? Access365 has the ability to use external data. You can have Access import the data into a table, or create a linked table which will always fetch the data from the external source.

If you decide to import, it can create a task to make doing it repeatedly very easy.

I hadn’t thought of that…Thanks!

Just when I think I’m out of the woods… engineering wants to link Altium to the DB to download our internal part numbers along with the associated manufacturer’s part numbers. IT doesn’t want to give engineering a profile to connect to the SQL database directly…I just want everyone to have the data they need… Does anyone have any experience with Altium? I feel like exporting as an XML file is easier in terms of keeping the data consistent but I don’t know if it’s possible for them to then link to it as a datasource.

I use Altium daily. Although it’s a probably a much older version.

I’m guessing they want to fill properties of schematic components/libraries

I wouldn’t think this would be something you’d link to the Epicor DB. Maybe just pull in once when the part/library is created. And maybe occasionally when the E10 DB changes and that Altium part needs to be updated.

I’d personally be hesitant to let my Altium files be automatically updated when the Epicor DB changes. Things could go bad real bad, real fast.

As far as giving users select data from Epicor DB, you can create data connections that only return the data specified by the connection.

Ohhh? I have SO much to learn!

Engineering likes to keep their library updated with all of our internal p/n’s and MPN so that when they are designing a board they can export the BOM with our p/n’s and it makes it easier for everyone.

Hi Wendy,
Just another thought. Could you solve your workflow/ report needs with a dashboard/BAQ(s), the Access DB attached as an external data source and use both the Epicor data and the Access data to drive a realtime solution instead of reports?

This seems to be an issue that just won’t behave and let me move on to something else. Thought we had it…but we didn’t. I tried creating a BAQ Report and was doing pretty good… had the report looking not too bad and then when I started formatting the fields to look the way I wanted, something went haywire and the report wouldn’t run anymore. I decided to delete it all and start from scratch which was apparently a bad idea as now I can’t get any portion of the process to work. I desperately need to take a class on BAQ and SSRS reporting…

In the past i have had luck modifying the import Excel or Access to Hard Code the part number as a String before importing. That way the “number” gets left alone.


If you are on a version later than 10.1.600 have you thought about using REST to pull into Excel the results of a BAQ?