Convert CSV file to Sheet and maintain drive file ID

Help and Support
1

STOP and READ USE THIS TEMPLATE NO EXCEPTIONS - By not using this, you waste your time, our time and really hate puppies. Please remove these two lines and that will confirm you have read them.

What is the problem you are having with rclone?

I have files that are being exported from a system in CSV format. I want to use RClone to sync and convert those files to Google Drive so they are Sheets files. Copy creates new files, Sync deletes old files and replaces them with new files. I need these files to maintain their original file ID from Google Drive, as these sheets are referenced and queried from other sheets.

Run the command 'rclone version' and share the full output of the command.

rclone v1.63.0
- os/version: Microsoft Windows Server 2016 Standard 1607 (64 bit)
- os/kernel: 10.0.14393.5989 (x86_64)
- os/type: windows
- os/arch: amd64
- go/version: go1.20.5
- go/linking: static
- go/tags: cmount

Which cloud storage system are you using? (eg Google Drive)

Google Drive

The command you were trying to run (eg rclone copy /tmp remote:tmp)

rclone copy "C:\Users\sftp\sftproot\gdrivesync" -v PSDataGDrive: --drive-import-formats csv --drive-allow-import-name-change --progress

A log from the command with the -vv flag

2023/07/06 14:06:05 DEBUG : rclone: Version "v1.63.0" starting with parameters ["rclone" "sync" "C:\\Users\\sftp\\sftproot\\gdrivesync" "-vv" "PSDataGDrive:" "--drive-import-formats" "csv" "--drive-allow-import-name-change" "--progress"]
2023/07/06 14:06:05 DEBUG : Creating backend with remote "C:\\Users\\sftp\\sftproot\\gdrivesync"
2023/07/06 14:06:05 DEBUG : Using config file from "C:\\Users\\admin-tep\\AppData\\Roaming\\rclone\\rclone.conf"
2023/07/06 14:06:05 DEBUG : fs cache: renaming cache item "C:\\Users\\sftp\\sftproot\\gdrivesync" to be canonical "//?/C:/Users/sftp/sftproot/gdrivesync"
2023/07/06 14:06:05 DEBUG : Creating backend with remote "PSDataGDrive:"
2023/07/06 14:06:05 DEBUG : PSDataGDrive: detected overridden config - adding "{7JH_0}" suffix to name
2023/07/06 14:06:05 DEBUG : fs cache: renaming cache item "PSDataGDrive:" to be canonical "PSDataGDrive{7JH_0}:"
2023-07-06 14:06:05 DEBUG : Attendance.csv: Need to transfer - File not found at Destination
2023-07-06 14:06:05 DEBUG : Courses.csv: Need to transfer - File not found at Destination
2023-07-06 14:06:05 DEBUG : Schedules.csv: Need to transfer - File not found at Destination
2023-07-06 14:06:05 DEBUG : Sections.csv: Need to transfer - File not found at Destination
2023-07-06 14:06:05 DEBUG : Students.CSV: Need to transfer - File not found at Destination
2023-07-06 14:06:05 DEBUG : Google drive root '': Waiting for checks to finish
2023-07-06 14:06:05 DEBUG : Google drive root '': Waiting for transfers to finish
2023-07-06 14:06:09 DEBUG : Sections.xlsx: Dst hash empty - aborting Src hash check
2023-07-06 14:06:09 INFO  : Sections.csv: Copied (new) to: Sections.xlsx
2023-07-06 14:06:10 DEBUG : Attendance.xlsx: Dst hash empty - aborting Src hash check
2023-07-06 14:06:10 INFO  : Attendance.csv: Copied (new) to: Attendance.xlsx
2023-07-06 14:06:11 DEBUG : Schedules.xlsx: Dst hash empty - aborting Src hash check
2023-07-06 14:06:11 INFO  : Schedules.csv: Copied (new) to: Schedules.xlsx
2023-07-06 14:06:11 DEBUG : Courses.xlsx: Dst hash empty - aborting Src hash check
2023-07-06 14:06:11 INFO  : Courses.csv: Copied (new) to: Courses.xlsx
2023-07-06 14:06:14 DEBUG : Students.xlsx: Dst hash empty - aborting Src hash check
2023-07-06 14:06:14 INFO  : Students.CSV: Copied (new) to: Students.xlsx
2023-07-06 14:06:14 DEBUG : Waiting for deletions to finish
2023-07-06 14:06:15 INFO  : Sections.xlsx: Deleted
2023-07-06 14:06:16 INFO  : Courses.xlsx: Deleted
2023-07-06 14:06:16 INFO  : Students.xlsx: Deleted
2023-07-06 14:06:16 INFO  : Schedules.xlsx: Deleted
2023-07-06 14:06:16 INFO  : Attendance.xlsx: Deleted
Transferred:        2.649 MiB / 2.649 MiB, 100%, 268.314 KiB/s, ETA 0s
Checks:                 5 / 5, 100%
Deleted:                5 (files), 0 (dirs)
Transferred:            5 / 5, 100%
Elapsed time:        11.2s
2023/07/06 14:06:16 INFO  :
Transferred:        2.649 MiB / 2.649 MiB, 100%, 268.314 KiB/s, ETA 0s
Checks:                 5 / 5, 100%
Deleted:                5 (files), 0 (dirs)
Transferred:            5 / 5, 100%
Elapsed time:        11.2s

2023/07/06 14:06:16 DEBUG : 14 go routines active
2

And what is the problem with this project of yours?

3

See above. When Rclone does a copy it simply creates a new sheets file with the same name containing the new data. When Rclone does a Sync it creates a new file with the new data and deletes the old sheet file, breaking all sharing, and any import functions, query functions, vlookup or xlookup functions that reference the remote sheet.

The desired output is that each sheet updates its data with the CSV data and retains its file ID so as to not break the above.

4

OK - I do not think you can do this with rclone or even with google drive API.

5

GAM tool can do it if you provide the file ID, but the hang-up with that is I have to maintain a MAP file that is a CSV that contains two columns, the local_file path and the mapped drive_file_id. Then I run a gam command that looks like this gam csv ".\_sync map.csv" gam user username@domain.org update drivefile ~Google_File_ID localfile ~Local_File_Name retainname

If rclone cashed that information somehow, it should be able to do whatever GAM is doing without the need for me to create a manual map file.

6

The API that creates files returns a files object that contains the driveId of the file that was created.

{
  "kind": string,
  "driveId": string, <------ the ID of the new file
  "fileExtension": string,
  "copyRequiresWriterPermission": boolean,
  "md5Checksum": string,
  "contentHints": {
    "indexableText": string,
    "thumbnail": {
      "image": string,
      "mimeType": string
    }
  },
...

So, if you are syncing a file from local storage to Drive for the first time, that response can be used to cache the mapping of the local file and the new file in Drive.

Then when performing a copy or sync, that cached map file can be referenced to ensure the file is updated and not deleted and replaced.

7

Yes it is interesting - thank you.

8

Rclone does in fact do this already for normal files

See how the ID does not change when updating the file.

$ date -Is | rclone rcat TestDrive:id-test/file.txt
$ rclone lsjson --stat TestDrive:id-test/file.txt
{
	"Path": "file.txt",
	"Name": "file.txt",
	"Size": 26,
	"MimeType": "text/plain",
	"ModTime": "2023-07-11T09:13:48.921Z",
	"IsDir": false,
	"ID": "1sWk11ahhEOPl0o9J8iUAJVz01W3gBFcs"
}
$ date -Is | rclone rcat TestDrive:id-test/file.txt
$ rclone lsjson --stat TestDrive:id-test/file.txt
{
	"Path": "file.txt",
	"Name": "file.txt",
	"Size": 26,
	"MimeType": "text/plain",
	"ModTime": "2023-07-11T09:14:07.174Z",
	"IsDir": false,
	"ID": "1sWk11ahhEOPl0o9J8iUAJVz01W3gBFcs"
}
$ echo "hello" | rclone rcat TestDrive:id-test/file.txt
$ rclone lsjson --stat TestDrive:id-test/file.txt
{
	"Path": "file.txt",
	"Name": "file.txt",
	"Size": 6,
	"MimeType": "text/plain",
	"ModTime": "2023-07-11T09:14:31.220Z",
	"IsDir": false,
	"ID": "1sWk11ahhEOPl0o9J8iUAJVz01W3gBFcs"
}

This does work fine with google docs also. In the course of experimenting I figured out your problem @TomP

You need to use --drive-export-formats csv otherwise rclone doesn't find the original document to update. You probably want to drop --drive-allow-import-name-change too.

$ echo "1,2,3" | rclone rcat --drive-export-formats csv --drive-import-formats csv TestDrive:id-test/file.csv
$ rclone lsjson --drive-export-formats csv --stat TestDrive:id-test/file.csv
{
	"Path": "file.csv",
	"Name": "file.csv",
	"Size": -1,
	"MimeType": "text/csv; charset=utf-8",
	"ModTime": "2023-07-11T09:17:36.226Z",
	"IsDir": false,
	"ID": "1YmH9WK-CWC5DnAYd3Iabzs4bJ0AvhQ_UEOwCeZ3JHzU"
}
$ rclone lsf  TestDrive:id-test/ # note that we need --drive-export-formats csv to see a csv file
file.txt
file.xlsx
$ echo "1,2,3,4" | rclone rcat --drive-export-formats csv --drive-import-formats csv TestDrive:id-test/file.csv
$ rclone lsjson --drive-export-formats csv --stat TestDrive:id-test/file.csv
{
	"Path": "file.csv",
	"Name": "file.csv",
	"Size": -1,
	"MimeType": "text/csv; charset=utf-8",
	"ModTime": "2023-07-11T09:18:47.618Z",
	"IsDir": false,
	"ID": "1YmH9WK-CWC5DnAYd3Iabzs4bJ0AvhQ_UEOwCeZ3JHzU"
}
9

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.