How to use import_csv

[eagle-k]

An explanation of how to use import_csv would be appreciated.

When a database instance is created using import_csv, is the corresponding schema generated implicitly?
If so, is there a way to use the implicitly generated schema, for example in a schema mapping?

Additionally, is there a way to import data while explicitly specifying the schema?

The examples in the manual available on the web seem to be outdated.

[wisnesky]

Hi, Csv import has recently been simplified and is likely to be buggier than usual for that reason. The best place to look is the built-in example called “CSV” (which contains some ignorable artifacts left over from the previous way CSV was handled). To obtain the schema of the CSV import programmatically, one would add: schema S0 = schemaOf I0 right below instance I0 = import_csv "." : sql the general form of csv import is: instance I = import_csv “/users/ryan/folder_with_lots_of_csv_files" : sql schema S = schemaOf I As before, CQL expects the import folder to have a collection of CSV files, with the names of the files serving as the entities. But as I see now, the entity names still contain the “.” from their path name; that’s an error I’ll fix.

…

-Ryan

On Jan 5, 2026, at 7:46 PM, Eagle ***@***.***> wrote: eagle-k created an issue (CategoricalData/CQL#95) An explanation of how to use import_csv would be appreciated. When a database instance is created using import_csv, is the corresponding schema generated implicitly? If so, is there a way to use the implicitly generated schema, for example in a schema mapping? Additionally, is there a way to import data while explicitly specifying the schema? The examples in the manual available on the web seem to be outdated. — Reply to this email directly, view it on GitHub, or unsubscribe. You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

[eagle-k]

Thank you for the quick response.

I am using the Sep 10, 2025 version of CQL on Windows.
The schema defined via schemaOf appears to work correctly, aside from the dot-related issue you mentioned.

However, I was unable to successfully define a schema mapping using the implicit schema obtained from the CSV import.
Below is a CQL example based on the official CSV sample, with a few additional lines added:

options
	always_reload = true

schema S0 = literal : sql {
	entities
		Employee
		Person
	foreign_keys
		eAsP : Employee -> Person
	attributes
		ssn : Person -> Integer
		eId : Employee -> Integer
		eSsn  : Employee -> Integer
	observation_equations
		eSsn = eAsP . ssn
}

#  Person.csv is the file
#   pId
#   0
#   1
#   2
#
#   Employee.csv is the file
#    eId,is
#    10,0
#    11,1
#    12,2

command createCsvData = exec_js {
	"Java.type(\"catdata.Util\").writeFile(\"pId\\n0\\n1\\n2\", \"Person.csv\")"
	"Java.type(\"catdata.Util\").writeFile(\"eId,is\\n10,0\\n11,1\\n12,2\", \"Employee.csv\")"
}

instance I0 = import_csv "." : sql 
schema SchemaOfI0 = schemaOf I0

mapping F = literal : SchemaOfI0 -> S0 {}

command exportCsvData = export_csv_instance I0 "exported"
command exportCsvData2 = export_csv_transform (identity I0) "exported_trans.csv"

At the mapping line, the following error is reported:

Non-equal typesides: typesideOf schemaOf I0 and sql

My current understanding is that import_csv "." : sql creates an instance over an implicit schema whose typeside is sql.
Therefore, I expected schemaOf I0 and S0 to share the same sql typeside.

I also have an additional concern.
If one wants to define schema mappings using schemas that are implicitly generated from CSV files, it seems that the schema must be fully determined at compile time. This would imply that the CSV files must already exist in order to define the schema mapping.

It seems that this issue would not arise if there were a way to import CSV data into a pre-declared, explicit schema.

[wisnesky]

The issue that "typesideOf (schemaOf X))" and “sql" are not syntactically the same but evaluate to the same typeside (sql) is a recurring issue in CQL, and other programming languages, where the compiler has to work around a lack of static knowledge. I think the new approach to importing CSV has made this issue more prevalent, and I’ll take a look at this particular error - there should be a way to make this work, if only by loosening compile time checking. As for importing on to existing schemas, that was the original way CQL worked. But it was untenable in practice (at large enterprises), because it required users to know both what schema they wanted, and what the schema was on disk (so that they could create a mapping from what it actually was to what they wanted; moreover, it seemed redundant to be able to specify mappings both at the import site and using the CQL mapping mechanism). Whereas with the new approach, one can avoid having to curate a new schema at all. That is, I claim any workflow should be the same or less work in the new approach (once the bugs are ironed out). That being said, from a theory point of view, to have a functor C -> D requires already knowing the categories C and D; to the extent there is polymorphism in CQL that works around this pragmatically, it is limited to the “constraint” kind (where the same constraints can be assigned multiple types).

…

On Jan 6, 2026, at 5:31 AM, Eagle ***@***.***> wrote: eagle-k left a comment (CategoricalData/CQL#95) <#95 (comment)> Thank you for the quick response. I am using the Sep 10, 2025 version of CQL on Windows. The schema defined via schemaOf appears to work correctly, aside from the dot-related issue you mentioned. However, I was unable to successfully define a schema mapping using the implicit schema obtained from the CSV import. Below is a CQL example based on the official CSV sample, with a few additional lines added: options always_reload = true schema S0 = literal : sql { entities Employee Person foreign_keys eAsP : Employee -> Person attributes ssn : Person -> Integer eId : Employee -> Integer eSsn : Employee -> Integer observation_equations eSsn = eAsP . ssn } # Person.csv is the file # pId # 0 # 1 # 2 # # Employee.csv is the file # eId,is # 10,0 # 11,1 # 12,2 command createCsvData = exec_js { "Java.type(\"catdata.Util\").writeFile(\"pId\\n0\\n1\\n2\", \"Person.csv\")" "Java.type(\"catdata.Util\").writeFile(\"eId,is\\n10,0\\n11,1\\n12,2\", \"Employee.csv\")" } instance I0 = import_csv "." : sql schema SchemaOfI0 = schemaOf I0 mapping F = literal : SchemaOfI0 -> S0 {} command exportCsvData = export_csv_instance I0 "exported" command exportCsvData2 = export_csv_transform (identity I0) "exported_trans.csv" At the mapping line, the following error is reported: Non-equal typesides: typesideOf schemaOf I0 and sql My current understanding is that import_csv "." : sql creates an instance over an implicit schema whose typeside is sql. Therefore, I expected schemaOf I0 and S0 to share the same sql typeside. I also have an additional concern. If one wants to define schema mappings using schemas that are implicitly generated from CSV files, it seems that the schema must be fully determined at compile time. This would imply that the CSV files must already exist in order to define the schema mapping. It seems that this issue would not arise if there were a way to import CSV data into a pre-declared, explicit schema. — Reply to this email directly, view it on GitHub <#95 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA2QKNY6UQF6GS3JNMYUDZT4FO2J3AVCNFSM6AAAAACQZITGJKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTOMJUG4YDQNZUGU>. You are receiving this because you commented.

[wisnesky]

These issues should be fixed in https://github.com/CategoricalData/CQL/releases/tag/Jan_7_2026 . Interestingly, the corresponding fix for queries (vs mappings) was already implemented. The new CSV example shows how to use both mappings and queries to project data from a CSV file.

…

On Jan 6, 2026, at 8:28 AM, Ryan Wisnesky ***@***.***> wrote: The issue that "typesideOf (schemaOf X))" and “sql" are not syntactically the same but evaluate to the same typeside (sql) is a recurring issue in CQL, and other programming languages, where the compiler has to work around a lack of static knowledge. I think the new approach to importing CSV has made this issue more prevalent, and I’ll take a look at this particular error - there should be a way to make this work, if only by loosening compile time checking. As for importing on to existing schemas, that was the original way CQL worked. But it was untenable in practice (at large enterprises), because it required users to know both what schema they wanted, and what the schema was on disk (so that they could create a mapping from what it actually was to what they wanted; moreover, it seemed redundant to be able to specify mappings both at the import site and using the CQL mapping mechanism). Whereas with the new approach, one can avoid having to curate a new schema at all. That is, I claim any workflow should be the same or less work in the new approach (once the bugs are ironed out). That being said, from a theory point of view, to have a functor C -> D requires already knowing the categories C and D; to the extent there is polymorphism in CQL that works around this pragmatically, it is limited to the “constraint” kind (where the same constraints can be assigned multiple types). > On Jan 6, 2026, at 5:31 AM, Eagle ***@***.***> wrote: > > > eagle-k > left a comment > (CategoricalData/CQL#95) > <#95 (comment)> > Thank you for the quick response. > > I am using the Sep 10, 2025 version of CQL on Windows. > The schema defined via schemaOf appears to work correctly, aside from the dot-related issue you mentioned. > > However, I was unable to successfully define a schema mapping using the implicit schema obtained from the CSV import. > Below is a CQL example based on the official CSV sample, with a few additional lines added: > > options > always_reload = true > > schema S0 = literal : sql { > entities > Employee > Person > foreign_keys > eAsP : Employee -> Person > attributes > ssn : Person -> Integer > eId : Employee -> Integer > eSsn : Employee -> Integer > observation_equations > eSsn = eAsP . ssn > } > > # Person.csv is the file > # pId > # 0 > # 1 > # 2 > # > # Employee.csv is the file > # eId,is > # 10,0 > # 11,1 > # 12,2 > > command createCsvData = exec_js { > "Java.type(\"catdata.Util\").writeFile(\"pId\\n0\\n1\\n2\", \"Person.csv\")" > "Java.type(\"catdata.Util\").writeFile(\"eId,is\\n10,0\\n11,1\\n12,2\", \"Employee.csv\")" > } > > instance I0 = import_csv "." : sql > schema SchemaOfI0 = schemaOf I0 > > mapping F = literal : SchemaOfI0 -> S0 {} > > command exportCsvData = export_csv_instance I0 "exported" > command exportCsvData2 = export_csv_transform (identity I0) "exported_trans.csv" > At the mapping line, the following error is reported: > > Non-equal typesides: typesideOf schemaOf I0 and sql > My current understanding is that import_csv "." : sql creates an instance over an implicit schema whose typeside is sql. > Therefore, I expected schemaOf I0 and S0 to share the same sql typeside. > > I also have an additional concern. > If one wants to define schema mappings using schemas that are implicitly generated from CSV files, it seems that the schema must be fully determined at compile time. This would imply that the CSV files must already exist in order to define the schema mapping. > > It seems that this issue would not arise if there were a way to import CSV data into a pre-declared, explicit schema. > > — > Reply to this email directly, view it on GitHub <#95 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA2QKNY6UQF6GS3JNMYUDZT4FO2J3AVCNFSM6AAAAACQZITGJKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTOMJUG4YDQNZUGU>. > You are receiving this because you commented. >

[eagle-k]

I tried the latest version, and the examples for schema mappings and queries now appear to be working correctly.
I would like to continue experimenting with it a bit more.

As a side question, when importing CSV files, is my understanding correct that the implicitly generated schema treats each file as an entity, with each column corresponding to an attribute of type String?

If this is the case, how should schema mappings be defined when either the source or the target schema contains attributes with non-String types?

[wisnesky]

The good news is that it’s easy to do this, by inserting a stringToInt conversion function on a typeside extending “sql”. I’ve updated the built-in CSV example to show this. The bad news is that the CSV importer was hardcoded to “sql” and not the typeside given by the user, so you’ll need to re-download the Jan 7 version for a fix. When writing your own functions on the sql typeside, be aware that every value is wrapped in a java “optional” class to reflect SQL null semantics.

…

On Jan 7, 2026, at 7:23 AM, Eagle ***@***.***> wrote: eagle-k left a comment (CategoricalData/CQL#95) <#95 (comment)> I tried the latest version, and the examples for schema mappings and queries now appear to be working correctly. I would like to continue experimenting with it a bit more. As a side question, when importing CSV files, is my understanding correct that the implicitly generated schema treats each file as an entity, with each column corresponding to an attribute of type String? If this is the case, how should schema mappings be defined when either the source or the target schema contains attributes with non-String types? — Reply to this email directly, view it on GitHub <#95 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA2QKN3LX3GNGS6DKBNY6IT4FUQI3AVCNFSM6AAAAACQZITGJKVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTOMJZGM4DONBUHE>. You are receiving this because you commented.