3
$\begingroup$

I need to batch convert a folder of CSVs containing UTM coordinates from GDA94 to GDA2020.

Sample data below:

enter image description here

Copyable form:

data = {{641680.276, 6.03860731*10^6, 1339.52}, {641725.276, 6.03832158*10^6,
   1334.2}, {641725.276, 6.03832056*10^6, 1335.08}, {641724.345, 
  6.03832158*10^6, 1335.08}, {641725.276, 6.03828587*10^6, 
  1351.28}, {641680.276, 6.03864303*10^6, 1313.68}, {641725.276, 
  6.0383573*10^6, 1323.63}, {641770.276, 6.0383191*10^6, 1306.73}}

I modified some code I found on here for a similar application to suit my needs (not best practice, I know). I am currently using +5 as the modifier for x and y for ease:

manipulateData[data_] := ({#1 + 5, #2 + 5, #3 + 0} & @@@ data);
Scan[Function[filename, 
  Module[{data, newdata, newfilename}, 
   Print["transforming " <> FileBaseName[filename] <> "..."];
   data = Import[filename, HeaderLines -> 1];
   newdata = Prepend[manipulateData[data], {"x", "y", "z"}];
   newfilename = 
    FileNameJoin[{DirectoryName[filename], 
      StringJoin[FileBaseName[filename], "_gda2020.csv"]}];
   Export[newfilename, newdata]]], 
 FileNames["*.csv", NotebookDirectory[]]]

This works well, and the only issue I'm having now is that the northing values (y) are exporting in scientific notation (6.04E+06 instead of 6038070.7) presumably because of the number of digits. The import data is numeric so it's something within the function or export.

I have tried a few applications of AccountingForm or ScientificNotationThreshold but haven't had any luck.

I am very new to this and appreciate any guidance.

EDIT---

I used @Syed's suggestions, substituting ToString@DecimalForm[#, {10, 3}] &@manipulateData[data] in place of Prepend[manipulateData[data], {"x", "y", "z"}] in the newdata function. Specifying the CSV format on export didn't seem to do anything so I left it out of the code.

This does stop scientific notation on export, but it also makes the exports lose formatting from the input file that was retained with the previous code.

New Output

{{641680.804, 6038608.736, 1339.518}, {641725.804, 6038323.004, 1334.197}, {641725.804, 6038321.980, 1335.077}, {641724.873, 6038323.004, 1335.077}, {641725.804, 6038287.287, 1351.280}, {641680.804, 6038644.453, 1313.677}, {641725.804, 6038358.720, 1323.632}, {641770.804, 6038320.518, 1306.729}

as opposed to

Original Output (with notepad screen below).

640848.704749,6038065.69986,1024.4 640846.898455,6038086.974,1024.4 640827.430614,6038091.59008,1024.4 640807.962774,6038096.20617,1024.4 640788.494934,6038100.82225,1024.4 640769.027094,6038105.43834,1024.4 640770.86206,6038127.6873,1024.4

enter image description here

In the past I have been able to get around this formatting with Flatten and TableForm, but I can't make that work in this instance. I also can't make Prepend work to insert headers.

Is there a way to correct this code to output headers and the correct formatting?

$\endgroup$

3 Answers 3

3
$\begingroup$

One idea (very similar to the one I gave in this answer), is to change the formatting of reals when using CForm (which is what "CSV" export uses). For example:

newdata = Prepend[manipulateData[data], {"x", "y", "z"}];

Internal`InheritedBlock[{Real},
    Unprotect[Real];
    Format[r_Real, CForm] := Format[DecimalForm[r, 10], OutputForm];
    ExportString[newdata, "CSV"]
]
"\"x\",\"y\",\"z\"
641685.2764,6038612.314,1339.52
641730.2764,6038326.582,1334.2
641730.2764,6038325.558,1335.08
641729.3453,6038326.582,1335.08
641730.2764,6038290.865,1351.28
641685.2764,6038648.031,1313.68
641730.2764,6038362.298,1323.63
641775.2764,6038324.096,1306.73
"
$\endgroup$
0
1
$\begingroup$

For two data items that I typed out:

r = ToString@DecimalForm[#, {10, 3}] & @ manipulateData[data]

Export["C:\\test.csv", r, "CSV"]
"{{646.680, 6038612.314, 1339.520}, {641730.276, 6038326.582, \
1334.200}}"

which also means that ToExpression will be used to convert quantities to numbers while importing.

EDIT-1

  • To maintain commas in the file like a CSV would have
  • To have DecimalForm output that OP wants
  • To have one data item per line without braces as lists would produce
  • Removing quotation marks around the strings (caveman tactic)

data = {{641680.2764, 6038607.314 , 1339.52},
   {641725.2764, 6038321.582, 1334.20}};

manipulateData[data_] := ({#1 + 5, #2 + 5, #3 + 0} & @@@ data);

t = manipulateData[data]

f2[y_List] := {DecimalForm[y[[1]], {13, 4}], 
  DecimalForm[y[[2]], {13, 4} ], DecimalForm[y[[3]], {13, 1} ]}

r = Map[f2, t, 1]

q = Map[ToString, r, {2}]
s = Prepend[q, {"x", "y", "z"}]

enter image description here


We are not done yet.

Export["C:\\test.csv", s, "CSV"]

enter image description here


I would keep it in this form. However, now there are two options: Manually replace the quotation marks or Import file as "Text" and rewrite after a simple StringReplace.

afile = Import["C:\\test.csv", "Text"]
bfile = StringReplace[afile, "\"" -> ""]
Export["C:\\test2.dat", bfile]

Your suggestions are welcome. I want to delete this solution.

$\endgroup$
2
  • $\begingroup$ Thanks Syed. I used your suggestions which worked for the original problem, but have left me with some formatting issues explained in the edit above. Could you point me towards a solution for these? $\endgroup$
    – JJ-
    Nov 11, 2021 at 8:45
  • 1
    $\begingroup$ It is quite a mess as I am discovering. Without strings the numbers revert to the scientific form. With strings you get quotation marks. Let me edit in what I have and you can decide if you want to use it or wait for other replies. $\endgroup$
    – Syed
    Nov 11, 2021 at 10:19
1
$\begingroup$

Also using DecimalForm

manipulateData[data_] := ({#1 + 5, #2 + 5, #3 + 0} & @@@ data)

(* get the data from the OP's image *)
(* TextRecognise works better on Mac than Windows in this case *)
dataimage = Import["https://i.stack.imgur.com/Pou0u.png"];
{header, datastr} = TakeDrop[StringSplit @@@
    ImportString[TextRecognize[dataimage], "TSV"], 1];

data = manipulateData[ToExpression[datastr]];

outputfile = "data.csv";

(* number to string converter, with max precision option *)
format[x_Real, precision_ : 6] := Module[{p},
  p = Max[Floor[Log10[Abs[x]]] + 1 + precision, 1];
  ToString[DecimalForm[N[x, p], p]]]

output = Map[StringRiffle[#, ", "] &,
   Prepend[data /. x_Real :> format[x], First@header]];
stream = OpenWrite[outputfile];
WriteString[stream, # <> "\n"] & /@ output;
Close[stream];

FilePrint[outputfile]
x, y, z
641685.2764, 6038612.314, 1339.52
641730.2764, 6038326.582, 1334.2
641730.2764, 6038325.558, 1335.08
641729.3453, 6038326.582, 1335.08
641730.2764, 6038290.865, 1351.28
641685.2764, 6038648.031, 1313.68
641730.2764, 6038362.298, 1323.63
641775.2764, 6038324.096, 1306.73

This is the same format in which Excel saves the CSV.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.