topic Re: Conversion from CSV to DBF altering data... in Python Questions

Conversion from CSV to DBF altering data...

DannyLackey — Thu, 04 Feb 2016 21:19:41 GMT

So, I successfully converted my .csv to a .dbf with this script:

env.workspace = r"c:\Output"

inTable = r"c:\Output\test.csv"
outLocation = r"c:\Output"
outTable = "test.dbf"

arcpy.TableToTable_conversion(inTable, outLocation, outTable)

The problem is, in the resulting .dbf file, its adding a decimal with trailing zeroes to the value:

750050

becomes

750050.00000000000

How can I avoid this?

Re: Conversion from CSV to DBF altering data...

DarrenWiens2 — Thu, 04 Feb 2016 21:26:02 GMT

I believe you'd have to use the Field Mapping parameter of TableToTable (which uses FieldMap and FieldMappings objects) to force the values into a field of LONG datatype.

Tip: set up the field mapping in the GUI tool, run it, then "Copy As Python Snippet" through the Results window to get the syntax correct.

Re: Conversion from CSV to DBF altering data...

XanderBakker — Thu, 04 Feb 2016 21:27:11 GMT

Not sure, but perhaps ArcGIS honors a schema.ini file: see Schema.ini File (Text File Driver)

Re: Conversion from CSV to DBF altering data...

ChrisSmith7 — Thu, 04 Feb 2016 21:36:14 GMT

I can confirm, ArcGIS does indeed honor schema.ini files - I use these when working with flat files/CSVs to ensure data types (e.g. to keep leading zeroes, etc).

Re: Conversion from CSV to DBF altering data...

DannyLackey — Thu, 04 Feb 2016 21:40:19 GMT

Ok. I have a schema.ini file. Do I need to point the script to that file in some way or does it simply need to be present in the same directory as the script or output?

Re: Conversion from CSV to DBF altering data...

ChrisSmith7 — Thu, 04 Feb 2016 21:43:35 GMT

Just make sure it's in the same directory - I create the schema.ini file in Python and write it to the directory where the table will go. So long as it's formatted correctly and in the same folder (you'll have to specify the target file in schema.ini), ArcGIS should pick-up on the driver to determine db structure. You can see this in action when viewing the tables in ArcCatalog.

Re: Conversion from CSV to DBF altering data...

DanPatterson_Retired — Thu, 04 Feb 2016 21:49:27 GMT

Adding an ASCII or text file table—Help | ArcGIS for Desktop

from the Overriding how text files are formatted section

ArcGIS uses its own implementation for accessing delimited text file information and should handle the most common cases for displaying these files in ArcGIS. As opposed to previous releases, schema.ini files, which are used by the Microsoft ODBC Text Driver for storing data description information, are not required for displaying delimited text file information in ArcGIS. However, if a schema.ini is present, ArcGIS will honor the settings specified in a schema.ini file to determine how the text file should be displayed.

It needs to be present where the text file will be/is according to this

Re: Conversion from CSV to DBF altering data...

DannyLackey — Thu, 04 Feb 2016 21:54:25 GMT

Ok. My schema.ini file was created when I made a connection to my .csv file in Toad. After looking at this file, I'm thinking it's not what I'm after. Can you explain how you created your schema.ini file to suit your needs?

Re: Conversion from CSV to DBF altering data...

DannyLackey — Thu, 04 Feb 2016 21:58:35 GMT

Ok, I found this example here: Does this method apply in my situation? http://stackoverflow.com/questions/12259562/how-can-i-create-schema-ini-file-i-need-to-export-my-csv-file-to-datagridvie…

Re: Conversion from CSV to DBF altering data...

DanPatterson_Retired — Thu, 04 Feb 2016 22:02:27 GMT

and look athe link inside that link Importing CSV file into Database with Schema.ini - Creating Schema.ini file dynamically or at run-time to import CSV fil…

and the requirements in there

Points to remember before creating Schema.ini

1. The schema information file, must always named as 'schema.ini'.

2. The schema.ini file must be kept in the same directory where the CSV file exists.

3. The schema.ini file must be created before reading the CSV file.

4. The first line of the schema.ini, must the name of the CSV file, followed by the properties of the CSV file, and then the properties of the each column in the CSV file.

and it is a text file... so create in a text editor, and explore with different options until you get it right. You can't foul up the csv file in any event... and even if that happened you would have your backup

Re: Conversion from CSV to DBF altering data...

DannyLackey — Thu, 04 Feb 2016 22:40:27 GMT

Something isn't working...

So, after having created the Schema.ini file containing this info:

[test.csv]

ColNameHeader=True

DateTimeFormat=dd-MMM-yyyy

Format=CSVDelimited

Col1=A Long

Col2=B Long

Col3=C DateTime

Col4=D DateTime

Col5=E Double

My conversion script is suddenly failing. As soon as I remove this Schema.ini file, it runs fine. Guessing there is something wrong with my Schema.ini file. Is it clear where I went wrong? It's in the same directory as the csv.

Re: Conversion from CSV to DBF altering data...

ChrisSmith7 — Thu, 04 Feb 2016 22:41:19 GMT

Regarding number 3, if, for some reason, you do skip to reading the CSV before creating the entries in an existing schema.ini file, it's really ok - ArcGIS should just append the file with some basic attributes - you can just edit with a text editor and adjust the structure as needed. You can have multiple entries in schema.ini for multiple files (in the same directory).

Also, if a schema.ini file doesn't exist, it's kinda handy that ArcGIS will create one upon first read. Good if you're being a lazy coder, lol. You can then make the edits directly in the schema.ini file and refresh in ArcCatalog/re-run your process.

Re: Conversion from CSV to DBF altering data...

ChrisSmith7 — Thu, 04 Feb 2016 22:45:27 GMT

Did you mean to format date with three Ms?

Re: Conversion from CSV to DBF altering data...

DannyLackey — Thu, 04 Feb 2016 22:48:38 GMT

I just grabbed that directly from the example. I thought about just removing it altogether and let it carry over the date as is, but figured I'd leave it in just in case.

Re: Conversion from CSV to DBF altering data...

DanPatterson_Retired — Thu, 04 Feb 2016 22:49:51 GMT

your column names are A,B, etc?

another visual

Using the Schema.ini File to Import a CSV File into ArcGIS | GISNuts.com

and make sure there no blank rows...especially at the end of the file

Re: Conversion from CSV to DBF altering data...

ChrisSmith7 — Thu, 04 Feb 2016 22:49:53 GMT

It should work - according to the documentation "All Microsoft Jet formats except A.M. and P.M. are supported."

Re: Conversion from CSV to DBF altering data...

DannyLackey — Thu, 04 Feb 2016 22:51:20 GMT

Well no, that's something else I grabbed from the example. It said you should copy that as is.... ay ay ay. Ok, so A, B, C etc, represents the actual column names... ? I'll try that and report back.

Re: Conversion from CSV to DBF altering data...

ChrisSmith7 — Thu, 04 Feb 2016 22:51:21 GMT

Also, did you rename your columns to A,B,C,D, etc?

Re: Conversion from CSV to DBF altering data...

DarrenWiens2 — Thu, 04 Feb 2016 22:52:40 GMT

Can you post an example of your data? This has got to be a simple fix.

Re: Conversion from CSV to DBF altering data...

DannyLackey — Thu, 04 Feb 2016 22:55:47 GMT

Here is a sample of my data:

X	Y	START_DTTM	END_DTTM	Value
1468050	750150	1/7/2016	1/31/2016	0.13237318
1468100	750150	1/7/2016	1/31/2016	-0.60834539
1468150	750150	1/7/2016	1/31/2016	-0.17450202
1468200	750150	1/7/2016	1/31/2016	-0.34773338
1467700	750100	1/7/2016	1/31/2016	-0.19723971
1467750	750100	1/7/2016	1/31/2016	0.16580771